Geek Answers Handbook

Case and Quantity in SQL Server 2008

I have a table that stores several items for the state, and I want to get an account for each state in accordance with 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 the sentence GROUP BYfor State_ID, I again got the error above for State_Name, and when the State_Name condition was added GROUP BY, I got an error for State_All, State_w, State_s.

I do not have a column State_All, State_w, State_s in my table.

How can I get an invoice according to specific conditions without use CURSORS?

+5
source share
4 answers

Will this be fixed?

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

.

COUNT . COUNT NULL ( ELSE CASE), . GROUP BY.

- type1 type2 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
+11

You can change the Chart 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
+3
source

At the end of the query, you should add both columns:

GROUP BY State_ID, State_Name
0
source

More articles:


All Articles