SQL statement - Multiple COUNT 1

[bslintx]

Counting on one field is all I ever needed, however, i need 3 this go around...

1 table, 400,000+ records, need to return distinct computers (GROUP BY <assuming) with a count on severity of 'Category I','Category II',Category III'

I currently have:

SELECT HOST,

(select count(severity) from table where severity = 'Category I') AS [Cat I],
(select count(severity) from table where severity = 'Category II') AS [Cat II],
(select count(severity) from table where severity = 'Category III') AS [Cat III]

FROM table
GROUP BY HOST

but returns:
HOST Cat I Cat II Cat III
comp1 73622 260071 270901
comp2 73622 260071 270901
comp3 73622 260071 270901

* host field is correct, however counts are not by host but database overall...

should return something like:

HOST Cat I Cat II Cat III
comp1 3 12 20
comp2 1 4 12
comp3 6 19 10
....


Thanks

 

[bborissov]

SELECT Host,
       SUM(CASE WHEN severity = 'Category I'
                     THEN 1
                ELSE 0 END) AS [Cat I],
       SUM(CASE WHEN severity = 'Category II'
                     THEN 1
                ELSE 0 END) AS [Cat II],
       SUM(CASE WHEN severity = 'Category III'
                     THEN 1
                ELSE 0 END) AS [Cat III]
FROM table
GROUP BY Host

If you use SQL Sever 2005 or above you could look for OVER (PARTITION BY ...) clause in BOL.


Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.

 

[bslintx]

I ended up with a 'pivoting' solution:

SELECT HOST,
COUNT(CASE WHEN Severity = 'Category I' THEN 1 END) AS
[Cat I],
COUNT(CASE WHEN Severity = 'Category II' THEN 1 END) AS
[Cat II],
COUNT(CASE WHEN Severity = 'Category III' THEN 1 END) AS [Category III]
FROM table 
GROUP BY [HOST];

Thanks

 

[bslintx]

Thanks bborissov!

I was 'googling' while you posted and came up with something very similar...i was surprised to see a fast reply!

a star for what ultimately would have been the answer

Thanks