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:

SELECT DRIVER_NAME, DRIVER_ID, CSC, SUM(DISTINCT(OBSERVATIONS)) AS OBSERVATIONS, --SUM(DISTINCT(INCIDENTS)) AS INCIDENTS_SUM_DISTINCT, 
        --COUNT(INCIDENTS) AS INCIDENTS,
        COUNT(INCIDENTS!='0'),
        CASE WHEN SUM(INCIDENTS) = '0' THEN '0' ELSE COUNT(INCIDENTS!='0') END AS INCIDENTS,
        (COUNT(INCIDENTS!='0') / SUM(DISTINCT(OBSERVATIONS))) * 100 AS FREQUENCY_PERCENT,
        1 AS GOAL, (SUM(SIX_TEN_MPH) / SUM(DISTINCT(OBSERVATIONS))) * 100 AS SIX_TEN_PERCENT,
    (SUM(ELEVEN_FIFTEEN_MPH) / SUM(DISTINCT(OBSERVATIONS))) * 100 AS ELEVEN_FIFTEEN_PERCENT, 
    (SUM(SIXTEEN_PLUS_MPH) / SUM(DISTINCT(OBSERVATIONS))) * 100 AS SIXTEEN_PLUS_PERCENT
        --SPEED_LIMIT, SPEED, DIFFERENCE, REPORT_DATE, TIME,  
FROM
(
  SELECT A.DRIVER_NAME AS DRIVER_NAME, A.DRIVER_ID AS DRIVER_ID, C.TRC_TERMINAL AS CSC, A.OBSERVATIONS AS OBSERVATIONS, 
    A.INCIDENTS AS INCIDENTS, B.SPEED_LIMIT AS SPEED_LIMIT, B.SPEED AS SPEED, B.DIFFERENCE AS DIFFERENCE,     
    A.REPORT_DATE AS REPORT_DATE, B.TIME AS TIME,    
  
    CASE WHEN DIFFERENCE >= 6 AND DIFFERENCE <= 10 THEN '1' WHEN DIFFERENCE IS NULL THEN '0' ELSE '0' END AS SIX_TEN_MPH,
    CASE WHEN DIFFERENCE > 10 AND DIFFERENCE <= 15 THEN '1' WHEN DIFFERENCE IS NULL THEN '0' ELSE '0' END AS ELEVEN_FIFTEEN_MPH,
    CASE WHEN DIFFERENCE > 15 THEN '1' WHEN DIFFERENCE IS NULL THEN '0' ELSE '0' END AS SIXTEEN_PLUS_MPH
  
  FROM "PROD"."PUBLIC"."SG_DRIVER_TREND" A
  LEFT JOIN "PROD"."PUBLIC"."SG_DRIVER_INCIDENTS" B
    ON A.DRIVER_ID = B.DRIVER_ID
  LEFT JOIN "PROD"."PUBLIC"."TMW_TRACTORPROFILE" C
    ON B.Vehicle = C.TRC_NUMBER 
    
  WHERE A.DRIVER_ID != ''  
    AND A.REPORT_DATE BETWEEN '2022-07-01' AND '2022-07-31'
    AND B.TIME BETWEEN '2022-07-01' AND '2022-07-31'
       AND SUBSTRING(B.TIME, 0, 10) <= A.REPORT_DATE                                          -- Less than or equal to report date
       AND SUBSTRING(B.TIME, 0, 10) > SUBSTRING(DATEADD(week,-1, A.REPORT_DATE), 0, 10)       -- Greater than 1 week ago
  
  UNION
  
  --SELECT A.DRIVER_NAME AS DRIVER_NAME, A.DRIVER_ID AS DRIVER_ID, C.TRC_TERMINAL AS CSC, A.OBSERVATIONS AS OBSERVATIONS, 
  --  A.INCIDENTS AS INCIDENTS, B.SPEED_LIMIT AS SPEED_LIMIT, B.SPEED AS SPEED, B.DIFFERENCE AS DIFFERENCE,     
  --  A.REPORT_DATE AS REPORT_DATE, B.TIME AS TIME,  
  SELECT DISTINCT(A.DRIVER_NAME) AS DRIVER_NAME, A.DRIVER_ID AS DRIVER_ID, 
            C.TRC_TERMINAL AS CSC, 
  --'UPT' AS CSC,
  A.OBSERVATIONS AS OBSERVATIONS, 
    '0' AS INCIDENTS, '0' AS SPEED_LIMIT, '0' AS SPEED, '0' AS DIFFERENCE,     
    A.REPORT_DATE AS REPORT_DATE, '' AS TIME, 
  
    '0' AS SIX_TEN_MPH,
    '0' AS ELEVEN_FIFTEEN_MPH,
    '0' AS SIXTEEN_PLUS_MPH
  
  FROM "PROD"."PUBLIC"."SG_DRIVER_TREND" A
  LEFT JOIN "PROD"."PUBLIC"."SG_DRIVER_INCIDENTS" B
   ON A.DRIVER_ID = B.DRIVER_ID
    
  --  AND SUBSTRING(B.TIME, 0, 10) <= A.REPORT_DATE                                          -- Less than or equal to report date
  --  AND SUBSTRING(B.TIME, 0, 10) > SUBSTRING(DATEADD(week,-1, A.REPORT_DATE), 0, 10)       -- Greater than 1 week ago
    
  INNER JOIN "PROD"."PUBLIC"."TMW_TRACTORPROFILE" C
    ON B.Vehicle = C.TRC_NUMBER 
    
     
  WHERE NOT EXISTS (SELECT DRIVER_ID FROM "PROD"."PUBLIC"."SG_DRIVER_INCIDENTS" D 
                    WHERE A.DRIVER_ID = D.DRIVER_ID 
                    AND D.TIME BETWEEN '2022-07-01' AND '2022-07-31'
                    AND SUBSTRING(D.TIME, 0, 10) <= A.REPORT_DATE                                          -- Less than or equal to report date 
                    AND SUBSTRING(D.TIME, 0, 10) > SUBSTRING(DATEADD(week,-1, A.REPORT_DATE), 0, 10)       -- Greater than 1 week ago
                   )                            
    AND A.DRIVER_ID != ''  AND A.INCIDENTS = '0' 
    AND A.REPORT_DATE BETWEEN '2022-07-01' AND '2022-07-31'
  --AND B.DRIVER_ID IS NULL     --<-- For LEFT JOIN
  ORDER BY DRIVER_ID    
)
--WHERE INCIDENTS != 0 
GROUP BY DRIVER_ID, DRIVER_NAME, CSC
ORDER BY DRIVER_ID

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.

Answer

The first line:

CASE WHEN SUM(INCIDENTS) = '0' THEN '0' ELSE COUNT(INCIDENTS!='0') END

is a conditional aggreation, simplified to COUNT_IF:

COUNT_IF(INCIDENTS != '0') AS INCIDENTS

The second one:

COUNT_IF(INCIDENTS != '0')/NULLIF(SUM(DISTINCT OBSERVATIONS)),0) * 100 
AS FREQUENCY_PERCENT

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

Advertisement

Answer