Reports: 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.

One row per customer

SELECT customer.name,
       COUNT(*) AS orders,
       ROUND(SUM(orders.total), 2) AS spent
FROM customer
INNER JOIN orders ON customer.id = orders.customer_id
GROUP BY customer.id, customer.name
ORDER BY customer.name;

Reading it in order: join the tables, group the rows by customer, then for each group count the orders and add up the totals.

Keeping customers with no qualifying orders

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.

Common mistakes

• Join first, then GROUP BY to summarise the joined rows.
• Qualify a column with its table name when both tables share it.