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

Question

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 where 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)

so i would like as results:

fips   sum  
 1     2.1  
 2     1.3  

I really hope that makes sense.

I've got: 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 ><

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.

Answer 1

Put the condition in WHERE instead of HAVING.

Answer 2

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