Skip to content
Advertisement

SQL Query to use a Case Statement within and Aggregate Function

I have a query below that I need to get a count where Incident Records = 0. The first level is a summary of the records it grabs to calculate totals. I tried using COUNT(INCIDENTS!=’0′) but it apparently is not working though it does not error out. How would I get this count? This is in Snowflake if that is a factor. The case statement: CASE WHEN SUM(INCIDENTS) = '0' THEN '0' ELSE COUNT(INCIDENTS!='0') END AS INCIDENTS Works but the 2nd line which references those Incidents seems to only give a count but not the result of the Case Statement before it:

This line:
CASE WHEN SUM(INCIDENTS) = '0' THEN '0' ELSE COUNT(INCIDENTS!='0') END AS INCIDENTS seems to give me the correct number of incidents but when I try and copy it into this line as the Incident Count: (COUNT(INCIDENTS!='0') / SUM(DISTINCT(OBSERVATIONS))) * 100 AS FREQUENCY_PERCENT like: (COUNT(CASE WHEN SUM(INCIDENTS) = '0' THEN '0' ELSE COUNT(INCIDENTS!='0') END) / SUM(DISTINCT(OBSERVATIONS))) * 100 AS FREQUENCY_PERCENT, I get the following error: SQL compilation error: Aggregate functions cannot be nested: [SUM(CAST("values".INCIDENTS AS FLOAT))] nested in [COUNT(IFF((SUM(CAST("values".INCIDENTS AS FLOAT))) = (CAST('0' AS FLOAT)), TO_NUMBER('0', 18, 0), COUNT(CAST("values".INCIDENTS != '0' AS BOOLEAN))))]

I have seen examples online of nested Aggregate Functions working. Code examples are appreciated.

Advertisement

Answer

The first line:

is a conditional aggreation, simplified to COUNT_IF:

The second one:

It is always good to secure against division by 0, thus NULLIF(exp, 0).

User contributions licensed under: CC BY-SA
7 People found this is helpful
Advertisement