CODEWITHSUNDEEP
sqlselectcountgroup-bywhere-clause
Musaab
Musaab

Reputation: 1604

SQL WHERE for a COUNT within a SELECT statement that has a GROUP BY

SELECT COUNT(pkNotification) AS caseTotal
,COUNT(fkCaseType) AS Suspected
, COUNT(fkCaseType) AS Confirmed
, Disease.Name 
FROM [Notification] 
INNER JOIN [Disease] ON Notification.fkDisease=Disease.pkDisease 
GROUP BY Disease.Name

That's my statement. But I need the COUNT(fkCaseType) AS Suspected to only be when fkCaseType=1 and for Confirmed to be when fkcaseType=2.

The problem is where I did subqueries, I had issues with the group by.

Upvotes: 3

Views: 661

Answers (1)

zerkms
zerkms

Reputation: 254956

COUNT(CASE WHEN fkCaseType = 1 THEN 1 END) Suspected,
COUNT(CASE WHEN fkCaseType = 2 THEN 1 END) Confirmed

In the first statement when fkCaseType = 1 - then returns 1 thus is counted by COUNT, NULL otherwise, which is skipped. For the second one - the same.

Upvotes: 6

Related Questions