Fixing 'column must appear in GROUP BY' by adding every column explodes the row count. Why?
Intended result — one row per customer with their total revenue. The first attempt raised column "order_date" must appear in the GROUP BY clause, so the analyst added every selected column to GROUP BY. Now it returns one row per order.
SELECT customer_id, order_date, SUM(amount) AS revenue
FROM orders
GROUP BY customer_id, order_date;Explain what went wrong and fix it to one row per customer.
The error means a selected column (order_date) is neither grouped nor aggregated. Adding every column to GROUP BY changes the grain to one group per distinct row, so nothing collapses. Fix by grouping on the real key (customer_id) alone, wrapping extras in MAX().
- ✗Adding every column to GROUP BY to silence the error
- ✗Thinking more GROUP BY columns shrink the result
- ✗Reaching for DISTINCT instead of fixing the grain
- →When is grouping by two columns actually what you want?
- →Why does SELECT * with GROUP BY rarely make sense?
The message column "order_date" must appear in the GROUP BY clause is telling you that order_date is in the SELECT list but is neither a grouping key nor inside an aggregate — SQL cannot decide which of a group's many order_date values to show.
Adding it to GROUP BY silences the error but changes the question: the grouping grain becomes (customer_id, order_date), so each distinct order date starts its own group. With one order per date, that is one group per order — the row count "explodes" back to the raw table.
Group by the real key only, and either drop order_date or aggregate it:
SELECT customer_id, SUM(amount) AS revenue
FROM orders
GROUP BY customer_id;
-- if you need a date, aggregate it:
SELECT customer_id, MAX(order_date) AS last_order, SUM(amount) AS revenue
FROM orders
GROUP BY customer_id;
