Vito Vito - 5 months ago 8
SQL Question

How to write count if group by officer?

I have to count every officer's cases where Survey='abc' and Activity='FI' and (outcome='C' OR outcome='D')

Here is my full SQL

SELECT officer, NULL,
SUM(IIF(ISNUMERIC(mkt) = TRUE AND Survey='dasda' AND Activity='FI' AND Outcome = 'C', Totalmin, 0 ) / 60),
SUM(Non), SUM(ICC), NULL, NULL, NULL, COUNT(IIF(Survey='abc' AND Activity='FI' AND (Outcome='C'or Outcome='D'), Totalmin, 0))
FROM [DB$]
GROUP BY officer " '


The problematic part is

Count(IIF( Survey='abc' and Activity='FI' and (Outcome='C'or Outcome='D'), Totalmin, 0 ))


However ,for the result that i generated doesn't work. The count result is far more than the answer .

Answer

COUNT() counts the number of non-NULL values. Because 0 is not null, it is counted. Here are two options:

COUNT(IIF(Survey='abc' AND Activity='FI' AND Outcome IN ('C', 'D'), Totalmin, NULL))

or

SUM(IIF(Survey='abc' AND Activity='FI' AND Outcome IN ('C', 'D') AND Totalmin IS NOT NULL, 1, 0))