GROUP BY and HAVING

GROUP BY makes one row per group#

SELECT category, COUNT(*) AS n, AVG(price) AS avg_price
FROM products
GROUP BY category;

products                 groups               output
electronics Mouse   ┐
electronics Keyboard┘──▶ electronics ──▶ electronics | 2 | 675
stationery  Pen     ┐
stationery  Notebook┘──▶ stationery  ──▶ stationery  | 2 | 35
home        Desk Lamp──▶ home        ──▶ home        | 1 | 700

MySQL forms the groups, then evaluates the aggregates once per group.

The rule behind ONLY_FULL_GROUP_BY#

Every selected column must either be in GROUP BY or be inside an aggregate.

SELECT category, name, COUNT(*) FROM products GROUP BY category;
-- ERROR 1055: 'name' isn't in GROUP BY

Which name should MySQL show for a category with two products? It can't know. Older MySQL versions picked one silently. Modern defaults refuse, which is the correct behavior. Do not turn the mode off to make errors vanish.

Multiple columns#

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

Groups are formed on the unique combination of both columns.

HAVING versus WHERE#

WHERE filters rows before grouping. HAVING filters groups after aggregation.

SELECT customer_id, SUM(oi.qty * oi.unit_price) AS spent
FROM orders o
JOIN order_items oi ON oi.order_id = o.id
WHERE o.status <> 'cancelled'        -- row filter, cheap
GROUP BY customer_id
HAVING spent > 500                   -- group filter
ORDER BY spent DESC;

If a condition doesn't involve an aggregate, put it in WHERE. It reduces the rows before the expensive grouping step, so it's faster, and it's clearer for the next reader.

WITH ROLLUP#

SELECT category, COUNT(*) FROM products GROUP BY category WITH ROLLUP;

Adds a subtotal row where category is NULL. It's handy for reports, but use GROUPING(category) to tell a rollup row from a real NULL.

Common mistakes#

• Putting a non-aggregate, ungrouped column in SELECT.
• Filtering with HAVING when WHERE would do.
• Grouping by an expression in one place and writing it slightly differently in another.
• Counting after a join and getting inflated numbers because the join multiplied rows.

Practice#

Write a query that shows, per customer, the number of orders and total spent, only for customers who have placed at least two orders. Then rewrite it so cancelled orders are excluded, and say which clause the exclusion goes in and why.