postgresql challenge: how to select sum of one column having another column like 'ab%'

fips   value   scc     param
 1      1      123     CO
 1      1.1    124     CO
 1      1.2    125     notCO
 2      1.3    122     CO
 2      1.4    142     CO

I would like to select the sum of value w开发者_如何学编程here scc starts with 12 and param="CO" and have them grouped by fips. SCC is unique per fips (as in multiple fips might have same scc)

I would like these results:

fips   sum  
 1     2.1  
 2     1.3  

I really hope that makes sense.

The query I'm currently using is:

SELECT
   fips
 , sum(value)
FROM
   table
WHERE
   param='CO'
GROUP BY
   fips
 , param
HAVING
   scc LIKE '12%'

but I have to have column scc in group by or aggregate, grrr.

If I include scc in group by, I get:

fips     sum    param
1        1       CO
1        1.1     CO
2        1.3     CO
2        1.4     CO

which is not summed at all. That's not what I want.

I'm attempting to simplify this. I actually have a bunch of other columns I hope shouldn't interfere (I could be way wrong), but I hope the design idea is there. If anyone has any suggestions, I really would be incredibly grateful.

If anyone has any questions that might help them help me, I'd love to hear it. Thanks to all.

Put the condition in WHERE instead of HAVING.

You probably should filter SSC in the where clause, not the having clause:

select fips, sum(value)
from YourTable
where param = 'CO'
and ssc like '12%'
group by fips