dimanche 15 mars 2015

Calculate extra column in group by

In sure this should be simple, but I'm having a brain fart over it.


In SQLServer 2005, how do you add an "extra count" to a GROUP BY query?


Consider the following...



;WITH DATA AS (
SELECT 1 AS ID, 1 AS TYPEID, 0 AS ACTIONNEEDED
UNION
SELECT 2 AS ID, 1 AS TYPEID, 0 AS ACTIONNEEDED
UNION
SELECT 3 AS ID, 2 AS TYPEID, 0 AS ACTIONNEEDED
UNION
SELECT 4 AS ID, 2 AS TYPEID, 1 AS ACTIONNEEDED
UNION
SELECT 5 AS ID, 2 AS TYPEID, 0 AS ACTIONNEEDED
)
SELECT TYPEID, COUNT(*) AS TOTAL
FROM DATA
GROUP BY TYPEID


The result is...



TYPEID TOTAL
1 2
2 3


But what I need is an extra column that totals the number of rows where ACTIONNEEDED=1...



TYPEID TOTAL ACTIONNEEDED
1 2 0
2 3 1


Note: unfortunately due to project constraints, I'm restricted to SQLServer 2005 compatible answers


Aucun commentaire:

Enregistrer un commentaire