Case and Count in SQL Server 2008

Question

I have a table that stores multiple items for a state and I want to get count for every states according to specific conditions. I wrote this query:

SELECT
    State_ID,
    State_Name,
    State_All= CASE WHEN type1=1 AND type2=1 THEN COUNT(Id) END
    State_w= CASE WHEN type1=2 AND type2=1 THEN COUNT(Id) END
    State_s= CASE WHEN type1=2 AND type2=2 THEN COUNT(Id) END
FROM
    tblStates

but I get this Error:

Column 'State_ID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

When I added GROUP BY clause For State_ID,I got above error again for State_Name,and when added State_Name to GROUP BY clause ,I got error for State_All,State_w,State_s.

I don't have a column called State_All,State_w,State_s in my table.

How I can get count according to specific conditions without using CURSORS?

Answer 1

You were on the right path.

You put the condition inside the COUNT like this. COUNT ignores NULLs (which is the implied ELSE in the CASE) so you only count true matches. You need the GROUP BY too.

Your error comes from the use of type1 and type2 outside of the COUNT

SELECT
    State_ID,
    State_Name,
    State_All = COUNT(CASE WHEN type1=1 AND type2=1 THEN 1 END), 
    State_w = COUNT(CASE WHEN type1=2 AND type2=1 THEN 1 END), 
    State_s = COUNT(CASE WHEN type1=2 AND type2=2 THEN 1 END)
FROM
    tblStates
GROUP BY
    State_ID, State_Name

Answer 2

You can change Count to SUM because each record result 1

SELECT
    State_ID,
    State_Name,
    State_All = SUM(CASE WHEN type1=1 AND type2=1 THEN 1 END), 
    State_w = SUM(CASE WHEN type1=2 AND type2=1 THEN 1 END), 
    State_s = SUM(CASE WHEN type1=2 AND type2=2 THEN 1 END)
FROM
    tblStates
GROUP BY
    State_ID, State_Name