SELECT COUNT(pkNotification) AS caseTotal
,COUNT(fkCaseType) AS Suspected
, COUNT(fkCa开发者_运维技巧seType) 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.
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.
精彩评论