HAVING and WITH ROLLUP: MySQL

Awaiting technical review. This lesson is readable but not yet listed in search engines.

By the end of this lessonFilter groups on aggregate values with HAVING and produce subtotals with WITH ROLLUP.

Filtering groups

You know WHERE filters rows. But what if the condition is about a group, such as "customers with more than one order" or "categories whose average price is above 30"? An aggregate like COUNT(*) does not exist until the rows are grouped, so WHERE COUNT(*) > 1 is not allowed. HAVING is the clause for conditions on groups.

sql

SELECT customer_id, COUNT(*) AS orders FROM orders GROUP BY customer_id HAVING COUNT(*) > 1 ORDER BY orders DESC, customer_id;

result

+-------------+--------+
| customer_id | orders |
+-------------+--------+
|           1 |      4 |
|           6 |      3 |
|           2 |      2 |
|           3 |      2 |
|           4 |      2 |
|           8 |      2 |
|          10 |      2 |
+-------------+--------+

The order of the clauses is GROUP BY, then HAVING, then ORDER BY. MySQL also lets you use a select-list alias in HAVING, so HAVING orders > 1 works here as well. Other databases may not allow that.

WHERE and HAVING together

They do different jobs and work well together. WHERE decides which rows go into the groups. HAVING decides which groups are kept:

sql

SELECT customer_id, COUNT(*) AS delivered_orders
FROM orders
WHERE status = 'delivered'
GROUP BY customer_id
HAVING COUNT(*) >= 2
ORDER BY delivered_orders DESC, customer_id;

result

+-------------+------------------+
| customer_id | delivered_orders |
+-------------+------------------+
|           1 |                3 |
|           4 |                2 |
+-------------+------------------+

Read it as: take only delivered orders, count them per customer, and keep customers with at least two.

A common beginner error is to put everything in HAVING. This works for the grouped column, for example HAVING customer_id = 1, but it is wasteful: MySQL builds every group first and then throws most away. A condition on a plain column belongs in WHERE, where it removes rows before the work of grouping starts.

	`WHERE`	`HAVING`
Runs	before grouping	after grouping
Can use aggregates	no	yes
Operates on	rows	groups

Examples of HAVING

Categories with an average price above 30:

sql

SELECT category_id, ROUND(AVG(price), 2) AS avg_price FROM products GROUP BY category_id HAVING AVG(price) > 30 ORDER BY avg_price DESC;

result

+-------------+-----------+
| category_id | avg_price |
+-------------+-----------+
|           1 |     88.37 |
|           2 |     36.48 |
|           3 |     35.25 |
|           5 |     34.99 |
+-------------+-----------+

Order lines worth more than 100 in total per order:

sql

SELECT order_id, SUM(quantity * unit_price) AS total FROM order_items GROUP BY order_id HAVING total > 100 ORDER BY total DESC;

result

+----------+--------+
| order_id | total  |
+----------+--------+
|       12 | 249.97 |
|       20 | 218.74 |
|        4 | 199.99 |
|       10 | 128.50 |
|       11 | 109.45 |
|        5 | 102.99 |
|       18 | 101.50 |
+----------+--------+

Several conditions are allowed, joined with AND and OR:

sql

SELECT status, COUNT(*) AS orders FROM orders GROUP BY status HAVING COUNT(*) >= 3 AND status <> 'delivered';

result

+---------+--------+
| status  | orders |
+---------+--------+
| shipped |      4 |
| pending |      3 |
+---------+--------+

WITH ROLLUP: subtotals and totals

Reports usually want totals as well. Add WITH ROLLUP after the GROUP BY list:

sql

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

result

+-----------+--------+
| status    | orders |
+-----------+--------+
| pending   |      3 |
| shipped   |      4 |
| delivered |     11 |
| cancelled |      2 |
| NULL      |     20 |
+-----------+--------+

The last row, where status is NULL, is the grand total. With two grouping columns you get a subtotal for each first-level value and a grand total:

sql

SELECT customer_id, status, COUNT(*) AS orders FROM orders WHERE customer_id IN (1, 6) GROUP BY customer_id, status WITH ROLLUP;

result

+-------------+-----------+--------+
| customer_id | status    | orders |
+-------------+-----------+--------+
|           1 | shipped   |      1 |
|           1 | delivered |      3 |
|           1 | NULL      |      4 |
|           6 | pending   |      1 |
|           6 | shipped   |      1 |
|           6 | delivered |      1 |
|           6 | NULL      |      3 |
|        NULL | NULL      |      7 |
+-------------+-----------+--------+

Read the NULL in the status column of a row with a customer id as "all statuses for this customer", and the row with both NULL as "everyone".

If a genuine NULL could appear in the data, you cannot tell it from a rolled-up row. GROUPING(column) returns 1 for generated rows and 0 otherwise, so you can label them:

sql

SELECT IF(GROUPING(status), 'ALL STATUSES', status) AS status, COUNT(*) AS orders FROM orders GROUP BY status WITH ROLLUP;

result

+--------------+--------+
| status       | orders |
+--------------+--------+
| pending      |      3 |
| shipped      |      4 |
| delivered    |     11 |
| cancelled    |      2 |
| ALL STATUSES |     20 |
+--------------+--------+

In MySQL 8 you can also sort a rollup query with ORDER BY, but because the NULL rows sort first, many people leave the sorting to the application.

Common mistakes

Using WHERE with an aggregate, which raises "Invalid use of group function".
Putting plain-column filters in HAVING, wasting work.
Leaving out GROUP BY when using HAVING. It treats the whole table as one group, which rarely makes sense.
Misreading ROLLUP NULLs as missing data.

Exercise

List the customers (by customer_id) who have placed at least two orders that are not cancelled, with the number of such orders. Then list the product categories with more than two products and add a grand total with WITH ROLLUP.

Show one possible answer

sql

SELECT customer_id, COUNT(*) AS orders
FROM orders
WHERE status <> 'cancelled'
GROUP BY customer_id
HAVING COUNT(*) >= 2
ORDER BY customer_id;

SELECT category_id, COUNT(*) AS products
FROM products
GROUP BY category_id WITH ROLLUP
HAVING COUNT(*) > 2;

result

+-------------+--------+
| customer_id | orders |
+-------------+--------+
|           1 |      4 |
|           2 |      2 |
|           3 |      2 |
|           4 |      2 |
|           6 |      3 |
|          10 |      2 |
+-------------+--------+
+-------------+----------+
| category_id | products |
+-------------+----------+
|           1 |        4 |
|           2 |        3 |
|           3 |        3 |
|           4 |        3 |
|        NULL |       15 |
+-------------+----------+

The second query keeps the rollup total row because 15 is greater than 2.

Key points

HAVING filters groups after aggregation, so it can use COUNT, SUM and AVG. WHERE filters rows before grouping and cannot.
Put conditions on plain columns in WHERE; they discard rows early and make the query faster.
Both HAVING and WHERE can appear in one query: WHERE shapes the groups, HAVING selects among them.
WITH ROLLUP adds subtotal and grand total rows; GROUPING() tells you which rows are those extra rows.

Filtering groups

WHERE and HAVING together

Examples of HAVING

WITH ROLLUP: subtotals and totals

Common mistakes

Exercise

Key points

Check yourself