What is GROUP BY and when do you need it?
SQLLevel: FoundationFundamentals (Easy)
What is GROUP BY and when do you need it?
Hints
- Define one output row and the source-table keys.
- Check duplicates, NULLs and the order of filtering and aggregation.
Answer
GROUP BY collapses rows that share the same value in the specified column(s) into a single row. You need it whenever you want per-group aggregates rather than a single aggregate across all rows.
-- Without GROUP BY: one result for all rows
SELECT COUNT(*) FROM orders; -- Total number of orders
-- With GROUP BY: one result per group
SELECT customer_id, COUNT(*) AS order_count
FROM orders
GROUP BY customer_id;
The rule: every column in SELECT that isn't inside an aggregate function must appear in GROUP BY.
Source and licence: SQL Interview Questions — Arda Kocadoru · MIT