Joins with grouping
The real power comes from combining what you have learned: join two tables, group the joined rows, and summarise each group with an aggregate.
A common report is "how much has each customer spent?" — join customers to their orders, group by customer, and SUM the totals.
SELECT customer.name,
MAX(orders.total) AS biggest,
ROUND(AVG(orders.total), 2) AS average
FROM customer
INNER JOIN orders ON customer.id = orders.customer_id
GROUP BY customer.id, customer.name
ORDER BY customer.name;
name | biggest | average
Ada | 25 | 17.25
Cara | 40 | 26
Read it in this order:
| Step | Clause | What happens |
|---|---|---|
| 1 | FROM … INNER JOIN … ON |
each order is joined to its customer: 4 joined rows |
| 2 | GROUP BY customer.id, customer.name |
the 4 rows become 2 groups: Ada's two orders, Cara's two orders |
| 3 | SELECT … MAX, AVG |
one result row per group |
| 4 | ORDER BY |
the result rows are sorted |
HAVING works on joined groups too. This keeps only customers who spent more than 40 in total:
SELECT customer.name, ROUND(SUM(orders.total), 2) AS spent
FROM customer
INNER JOIN orders ON customer.id = orders.customer_id
GROUP BY customer.id, customer.name
HAVING SUM(orders.total) > 40;
name | spent
Cara | 52
Ada spent 25 + 9.50 = 34.50, so her group is removed.
An INNER JOIN drops Ben and Dan because they have no orders. A LEFT JOIN keeps each customer and supplies NULLs for an unmatched order. COUNT(*) would count that placeholder row as one; COUNT(orders.id) skips its NULL id and correctly gives zero. SUM returns NULL when there are no non-NULL totals, so use COALESCE(..., 0) for a zero-spend report.
To count only orders of at least 25 while keeping every customer, put the order condition in ON:
SELECT customer.name, COUNT(orders.id) AS qualifying_orders,
COALESCE(ROUND(SUM(orders.total), 2), 0) AS spent
FROM customer
LEFT JOIN orders ON customer.id = orders.customer_id AND orders.total >= 25
GROUP BY customer.id, customer.name
ORDER BY customer.name;
The result is Ada: 1 and 25; Ben: 0 and 0; Cara: 1 and 40; Dan: 0 and 0. Moving orders.total >= 25 to WHERE would remove Ben and Dan: their NULL totals do not satisfy that comparison. Group by the id as well as the name so two customers with the same name remain separate.
- Join first, then
GROUP BYto summarise the joined rows. - Qualify a column with its table name when both tables share it.
Joining tables
An inner join drops rows that have no match.
Độ khó 4/5 · 280 xu + phần thưởng nhỏ khi hoàn thành lần đầu tiên hôm nay
For each customer who has orders, show their name, their number of orders as orders, and their total spend (2 d.p.) as spent. Join, group by customer.id, and order by name.
Nhấn Chạy để xem kết quả ở đây.
Độ khó 4/5 · 280 xu + phần thưởng nhỏ khi hoàn thành lần đầu tiên hôm nay
An INNER JOIN hid Ben and Dan, who have no orders. Use a LEFT JOIN to keep every customer, and COUNT(orders.id) so a customer with no orders shows 0. Order by name.
Nhấn Chạy để xem kết quả ở đây.
Độ khó 4/5 · 280 xu + phần thưởng nhỏ khi hoàn thành lần đầu tiên hôm nay
Keep every customer, including those with no qualifying orders. Show name, the number of orders with total > 30 as orders, and their total as spent (round to 2 d.p., or 0 if none). Order by name. Place the filter so unmatched customers survive.
Nhấn Chạy để xem kết quả ở đây.