开发者

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

开发者 https://www.devze.com 2023-03-08 11:51 出处:网络
SELECT COUNT(pkNotification) AS caseTotal ,COUNT(fkCaseType) AS Suspected , COUNT(fkCa开发者_运维技巧seType) AS Confirmed
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.

0

精彩评论

暂无评论...
验证码 换一张
取 消