WHERE clause expression references [casted timestamp to date column] which is neither grouped nor aggregated at

This query should sum up all the records by Error_Type (nested) and by Application(nested) in the Failed_Record_Count column. Total_Record_Count column should sum up all the records from the table(excluding unnested tables). However, an error referencing to a.timestamp in the subquery is said to be neither grouped nor aggregated.

I noticed that when removing the “DATE” function from the select list, it successfully executes but doesn’t have my expected output. I am expecting that it would be grouped by date and not by timestamp, thus, Im trying to sum up the records by DATE and not by timestamp.

Please help.

SELECT
    DATE(timestamp) AS Date,
    status AS Status,
    program_number AS P_No,
    flow AS Flow,
    e.type AS Error_Type,
    e.application AS Application,
    (
        SELECT
            COUNT(*)
        FROM
            `table` b
        WHERE
            a.status = b.status
            AND a.program_number = b.program_number
            AND a.flow = b.flow
            AND DATE(a.timestamp) = DATE(b.timestamp)
    ) AS Total_Record_Count,
    COUNT(*) AS Failed_Record_Count
FROM
    `table` a,
    UNNEST(evaluation.validation) as ev,
    UNNEST(ev.errors) as e
WHERE
    DATE(timestamp) BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 2 DAY)
    AND CURRENT_DATE()
GROUP BY 1,2,3,4,5,6
ORDER BY 2,3,4,5,6

Answer

The sub query is not considered as an aggregate function in the outer select statement. Hence, you have to add field 7 to group by as shown below:

SELECT
    DATE(timestamp) AS Date,
    status AS Status,
    program_number AS P_No,
    flow AS Flow,
    e.type AS Error_Type,
    e.application AS Application,
    (
        SELECT
            COUNT(*)
        FROM
            `table` b
        WHERE
            a.status = b.status
            AND a.program_number = b.program_number
            AND a.flow = b.flow
            AND DATE(a.timestamp) = DATE(b.timestamp)
    ) AS Total_Record_Count,
    COUNT(*) AS Failed_Record_Count
FROM
    `table` a,
    UNNEST(evaluation.validation) as ev,
    UNNEST(ev.errors) as e
WHERE
    DATE(timestamp) BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 2 DAY)
    AND CURRENT_DATE()
GROUP BY 1,2,3,4,5,6,7
ORDER BY 2,3,4,5,6