Field must appear in the GROUP BY clause or be used in an aggregate function

Question

I'm trying to write a query containing subqueries, which contain aggregate functions. I get an error message telling me that I should include some of my fields in the GROUP BY clause of my main query, in which I don't use any aggregates. I'm pretty new at SQL so this might be a silly mistake but I have been on it for days and I can't figure it out with the existing content.

WITH B AS 
(
  SELECT
    A.Week
   ,A.ASIN
   ,A.GL
   ,sum(Case when Week = 'W1' then Total_GV_Price/Total_GVs else 0 end) as Wk1_AVG
   ,sum(Case when Week = 'W2' then Total_GV_Price/Total_GVs else 0 end) as Wk2_AVG
 FROM A
 WHERE Total_GVs > 0
 GROUP BY A.Week, A.ASIN, A.GL
)
, C AS 
(
  SELECT
    B.Week
   ,B.ASIN
   ,B.GL
   ,B.Wk1_AVG
   ,B.Wk2_AVG
   ,Case when NVL(Wk1_AVG,0) =0 then NULL else (Wk2_AVG - Wk1_AVG) / Wk1_AVG end as Price_var
  FROM B
  GROUP BY B.Week, B.ASIN, B.GL, B.Wk1_AVG, B.Wk2_AVG
)
SELECT C.*
FROM C
HAVING Price_var >= 0.3

So the error message is telling me: column "c.week" must appear in the GROUP BY clause or be used in an aggregate function. And I don't understand what i'm not grouping or what I should be grouping.

I appreciate any comments and thank you for your help !

Remove the GROUP BY clause from the C subquery. You are not aggregating anything there. And change HAVING to WHERE in your main query, because again you are not aggregating anything there. — Thorsten Kettner
So calculations within a CASE are not considered as aggregations? It worked :) — Jonathan
@Jonathan: aggregation is the use of an aggregation function e.g. sum(), avg(), min() or max() — a_horse_with_no_name
The error message seems to indicate you are using PostgreSQL. However the nvl() function seems to indicate you are using Oracle. Which one is it? — a_horse_with_no_name
@Jonathan: It works, because your GROUP BY clause doesn't do anything. You say you want to aggregate rows so as to have no duplicates per Week/ASIN/GL/Wk1_AVG/Wk2_AVG, but there are none, which you ensured in your B query. CASE WHEN doesn't aggregate, because it deals with values of a single row. Aggregation means to take values from multiple rows and end up with one value (say the maximum, minimum, or average of the values). — Thorsten Kettner

R. Horber R. Horber · Accepted Answer · 2019-01-24T10:22:09

I'm not sure, because I can't reproduce the situation. But I think the problem lies in the SELECT ... FROM C at the end. You are using HAVING, which is used for aggregated columns (i.e. SUM(...)). Because the last SELECT has no aggregation a simple WHERE would suffice.

...
SELECT
C.*
FROM
C
WHERE Price_var >= 0.3

Hope this helps...

Field must appear in the GROUP BY clause or be used in an aggregate function

1 Answers