Vito Vito - 3 months ago 6
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))
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 .


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))


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