Case statement with In()

[jmj]

I am running queries to do a count of data (How many males/females, how many speakers of spanish/french/etc).
All is fine as long as the case statement uses an = sign. If I need an in clause (CASE WHEN EnglisProf IN('A', 'B') then count(EnglisProf) else 0 end as EnglisProf1) then it will only count IF there are all 'A' or all 'B'.
I have worked around this by splitting them out to ='A', ='B' and then using a sum.
This would be ok, but I have one field can hold 12 different codes. Out of these, I need to count up 9 of these codes.
Is there a more eloquent solution then counting each code separately and then summing it? This seems a bit cumbersome.
sum(EnglisProfA + EnglisProfB + EnglisProfC + EnglisProfD)

CASE WHEN EnglisProf ='A' then count(EnglisProf) else 0 end as EnglisProfA
CASE WHEN EnglisProf ='B' then count(EnglisProf) else 0 end as EnglisProfB
CASE WHEN EnglisProf ='C' then count(EnglisProf) else 0 end as EnglisProfC
CASE WHEN EnglisProf ='D' then count(EnglisProf) else 0 end as EnglisProfD

Thanks for any ideas about this!
J

 

[NoCoolHandle]

Take a look at Compute and ComputeBy

They produce NonRelational Output (not good for recordsets - more like a report)

Also

Look at CUBE and ROLEUP... (and the "grouping()" function)
They do produce relational data. You need to spend some time looking at the results but they are probably suitable for what you are doing..

Something like

Select EnglisProf, Count(EnglisProf) from Table with Roleup group by EnglishProf