GROUP BY — One Answer per Group
"Total items sold" is one number. "Total items sold per customer" is a small table: one line for each customer. That little word per is the signal for GROUP BY, which runs an aggregate separately for each group of rows instead of once for the whole table.
The rule
Every selected column is either in GROUP BY or inside an aggregate.
| order_id | customer_id | product_id | quantity | status |
|---|---|---|---|---|
| 101 | 1 | 1 | 2 | shipped |
| 102 | 2 | 3 | 1 | pending |
| 103 | 1 | 2 | 5 | shipped |
| 104 | 3 | 1 | 1 | cancelled |
| 105 | 3 | 2 | 3 | shipped |
SELECT customer_id, SUM(quantity) AS items
FROM orders
GROUP BY customer_id;| customer_id | items |
|---|---|
| 1 | 7 |
| 2 | 1 |
| 3 | 4 |
GROUP BY sorts the rows into piles — one pile per distinct customer_id — and SUM runs once per pile. Customer 1's pile holds orders 101 and 103, so 2 + 5 = 7.
Now the rule. customer_id is in GROUP BY, so each pile has exactly one value of it and it can be shown. SUM(quantity) is an aggregate, so it produces one value per pile. But status is neither: customer 1's pile has only "shipped", but customer 3's has "cancelled" and "shipped", so there is no single answer. Adding status to that SELECT list is rejected by PostgreSQL, SQL Server and modern MySQL.
You can group by several columns (GROUP BY customer_id, status makes a pile for each combination), and WHERE still runs first, removing rows before they are grouped. Rows with NULL in the grouping column form one group of their own. The order of the output groups is not guaranteed, so add ORDER BY when it matters.
The shortcut
Find the word "per" (or "for each", "by") in the question. Whatever follows it goes in GROUP BY and in SELECT. Everything else in SELECT must be an aggregate.
Find "per"
"Orders per status" — status is the group.
Drills
Answer fast — trust the rule.
In SELECT customer_id, SUM(quantity) FROM orders GROUP BY customer_id;, what is customer 3's total?
Why is SELECT customer_id, status, SUM(quantity) FROM orders GROUP BY customer_id; rejected by most databases?
How many rows does SELECT status, COUNT(*) FROM orders GROUP BY status; return?
Which query gives the number of customers per city?
Remember
- Every selected column is either in GROUP BY or inside an aggregate.
- Find "per": that column goes in GROUP BY and in SELECT.
- WHERE removes rows before grouping; add ORDER BY if the order of groups matters.