Geek asks and answers

SQL WHERE for COUNT in a SELECT statement that has 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

This is my statement. But I need COUNT (fkCaseType) AS. It is assumed that only when fkCaseType = 1, and for the confirmed - fkcaseType = 2.

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

+3
source share
1 answer
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, therefore it is counted COUNT, NULLotherwise it is skipped. For the second - the same thing.

+6
source

More articles:


All Articles