Summarizing rows: GROUP BY

One number per group

GROUP BY collects rows that share a value, and aggregate functions reduce each group to a single value.

SELECT status, COUNT(*) AS orders
FROM orders
GROUP BY status
ORDER BY orders DESC;

Seven statuses produce seven rows. Every item in the SELECT list must be a grouping column or an aggregate. Otherwise the result is ambiguous: if you also select order_number, which of the 1,351 delivered orders should be shown?

COUNT(*) is not COUNT(column)

SELECT COUNT(*) AS customers,
       COUNT(phone) AS with_phone,
       COUNT(*) - COUNT(phone) AS without_phone
FROM customers;

COUNT(*) counts all rows. COUNT(phone) counts only rows where phone is not NULL. Their difference is the number of missing phone values, so no second query is needed.

Every other aggregate skips NULLs too, which matters most for AVG. The average of 10, 20 and NULL is 15, not 10 — the NULL is not treated as a zero, it is not counted at all. When you do want it treated as zero, say so with COALESCE(column, 0).

Several aggregates at once

SELECT cat.name AS category,
       COUNT(*) AS products,
       ROUND(MIN(p.price), 2) AS cheapest,
       ROUND(AVG(p.price), 2) AS average,
       ROUND(MAX(p.price), 2) AS dearest
FROM products p
JOIN categories cat ON cat.id = p.category_id
GROUP BY cat.id
ORDER BY average DESC
LIMIT 20;

The database calculates five values for each category in one pass over the table. Doing the same work in application code would mean transferring and processing all 400 product rows; the query returns only the 20 summary rows.

Grouping across a join

Aggregates and joins can be combined: join the tables first, then group the resulting rows.

SELECT cat.name AS category,
       COUNT(DISTINCT o.id) AS orders,
       SUM(oi.quantity) AS units,
       ROUND(SUM(oi.quantity * oi.unit_price), 2) AS revenue
FROM order_items oi
JOIN orders o ON o.id = oi.order_id
JOIN products p ON p.id = oi.product_id
JOIN categories cat ON cat.id = p.category_id
WHERE o.status IN ('paid', 'shipped', 'delivered')
GROUP BY cat.id
ORDER BY revenue DESC;

WHERE before, HAVING after

Both clauses filter results, but at different stages of the query.

SELECT cat.name AS category,
       ROUND(SUM(oi.quantity * oi.unit_price), 2) AS revenue
FROM order_items oi
JOIN products p ON p.id = oi.product_id
JOIN categories cat ON cat.id = p.category_id
GROUP BY cat.id
HAVING SUM(oi.quantity * oi.unit_price) > 1000000
ORDER BY revenue DESC;

WHERE runs before grouping and decides which rows take part. HAVING runs after and decides which groups survive. So a condition on a raw column goes in WHERE, and a condition on an aggregate can only go in HAVING — the aggregate does not exist yet while WHERE is running.

The clause sequence from the SELECT lesson makes the distinction clear:

FROM  ->  WHERE  ->  GROUP BY  ->  HAVING  ->  SELECT  ->  ORDER BY  ->  LIMIT

Grouping by something you compute

You can group by an expression, not only a column. Cutting the first seven characters off a timestamp gives 2025-01 — a month.

SELECT substr(ordered_at, 1, 7) AS month,
       COUNT(*) AS orders,
       ROUND(SUM(total), 2) AS revenue
FROM orders
WHERE status IN ('paid', 'shipped', 'delivered')
  AND ordered_at >= '2025-01-01'
GROUP BY month
ORDER BY month;

This is the usual pattern for reports grouped by month or week. Here GROUP BY month reuses the SELECT alias. All of these engines allow that in GROUP BY and ORDER BY, but not in WHERE or HAVING.

What you can do now

You can produce a report. What you cannot yet do is compare a row against a summary — "products priced above the average", "the top three in each category" — because that needs two queries at once. The next lesson covers it.