CODEWITHSUNDEEP
sqlgroup-byteradata
Romero Azzalini
Romero Azzalini

Reputation: 304

SQL Selected non-aggregate values must be part of the associated group

I do not get what I am missing here. To me it seems like I included every non-aggregate value in the select statement

SELECT TOP 5
    f_ev2.Identifier,
    AVG(f_ev2.Identifier) AS Identifier,
    MIN(f_ev2.Event_dtm) AS Erster_PZ_Scan
FROM DB_DWH_MART_AKM_PLT.VW_F_EVENT f_ev2
INNER JOIN
    (
        SELECT 
        DISTINCT f_ev.AUFTRAGGEBER_EKP AS EKP
        FROM    DB_DWH_MART_AKM_PLT.VW_F_EVENT f_ev
        WHERE f_ev.PROCESS_NO = 1075
    ) AS manifest_users
ON f_ev2.AUFTRAGGEBER_EKP = manifest_users.EKP
WHERE f_ev2.event_dtm BETWEEN (date -1) AND  (date -10)
GROUP BY f_ev2.Identifier
HAVING (f_ev2.PROCESS_NO = 1 OR f_ev2.PROCESS_NO = 2)

Why do I get

SQL Selected non-aggregate values must be part of the associated group

Upvotes: 0

Views: 179

Answers (1)

dnoeth
dnoeth

Reputation: 60472

After aggregation there's no more detail, i.e. f_ev2.PROCESS_NO.

You can either move the condition to WHERE

AND f_ev2.PROCESS_NO IN (1, 2)

or apply an aggregate function, e.g.

HAVING MIN(f_ev2.PROCESS_NO IN (1, 2)

But following condition will always return an empty result set, Explain will reveal an unsatisfiable condition:

WHERE f_ev2.event_dtm BETWEEN (date -1) AND  (date -10)

You must switch the dates:

WHERE f_ev2.event_dtm BETWEEN (current_date -10) AND  (current_date -1)

Upvotes: 1

Related Questions