MySQL WITH ROLLUP: Subtotals and Grand Totals in One Query

Reports often need subtotals and a grand total. WITH ROLLUP adds them to a GROUP BY result.

query.sql

SQL

CREATE TABLE sales (
    region VARCHAR(10) NOT NULL,
    product VARCHAR(10) NOT NULL,
    amount DECIMAL(8,2) NOT NULL
);
INSERT INTO sales VALUES
    ('north', 'pen',  100), ('north', 'book', 250), ('north', 'pen', 50),
    ('south', 'pen',  300), ('south', 'book', 120),
    ('west',  'book', 500);

SELECT region, SUM(amount) AS total
FROM sales
GROUP BY region WITH ROLLUP;

Output

OUTPUT

+--------+---------+
| region | total   |
+--------+---------+
| north  |  400.00 |
| south  |  420.00 |
| west   |  500.00 |
| NULL   | 1320.00 |
+--------+---------+
4 rows in set

The last row, with NULL in region, is the grand total. It sums all the groups.

Two levels

query.sql

SQL

1
2
3

SELECT region, product, SUM(amount) AS total
FROM sales
GROUP BY region, product WITH ROLLUP;

Output

OUTPUT

+--------+---------+---------+
| region | product | total   |
+--------+---------+---------+
| north  | book    |  250.00 |
| north  | pen     |  150.00 |
| north  | NULL    |  400.00 |
| south  | book    |  120.00 |
| south  | pen     |  300.00 |
| south  | NULL    |  420.00 |
| west   | book    |  500.00 |
| west   | NULL    |  500.00 |
| NULL   | NULL    | 1320.00 |
+--------+---------+---------+
9 rows in set

You get the detail rows, a subtotal row for each region (where product is NULL) and the grand total (both NULL). Rollup works from the right to the left: it removes the last grouping column first.

Telling a subtotal NULL from a real NULL

A NULL in the result can mean “this is a total row” or “the data has a NULL here”. The GROUPING() function tells them apart. It returns 1 for a super-aggregate row and 0 otherwise:

query.sql

SQL

SELECT IF(GROUPING(region), 'ALL REGIONS', region)    AS region,
       IF(GROUPING(product), 'ALL PRODUCTS', product) AS product,
       SUM(amount) AS total,
       GROUPING(region) AS g_region,
       GROUPING(product) AS g_product
FROM sales
GROUP BY region, product WITH ROLLUP;

Output

OUTPUT

+-------------+--------------+---------+----------+-----------+
| region      | product      | total   | g_region | g_product |
+-------------+--------------+---------+----------+-----------+
| north       | book         |  250.00 |        0 |         0 |
| north       | pen          |  150.00 |        0 |         0 |
| north       | ALL PRODUCTS |  400.00 |        0 |         1 |
| south       | book         |  120.00 |        0 |         0 |
| south       | pen          |  300.00 |        0 |         0 |
| south       | ALL PRODUCTS |  420.00 |        0 |         1 |
| west        | book         |  500.00 |        0 |         0 |
| west        | ALL PRODUCTS |  500.00 |        0 |         1 |
| ALL REGIONS | ALL PRODUCTS | 1320.00 |        1 |         1 |
+-------------+--------------+---------+----------+-----------+
9 rows in set

Now the totals have readable labels, and the application can recognise them from g_region and g_product.

Sorting a rollup

ORDER BY with WITH ROLLUP is limited: the totals would be sorted along with the rest. A common trick sorts by the GROUPING() values, so totals stay at the end of each block:

query.sql

SQL

SELECT region, product, SUM(amount) AS total
FROM sales
GROUP BY region, product WITH ROLLUP
ORDER BY GROUPING(region), region, GROUPING(product), product;

Output

OUTPUT

+--------+---------+---------+
| region | product | total   |
+--------+---------+---------+
| north  | book    |  250.00 |
| north  | pen     |  150.00 |
| north  | NULL    |  400.00 |
| south  | book    |  120.00 |
| south  | pen     |  300.00 |
| south  | NULL    |  420.00 |
| west   | book    |  500.00 |
| west   | NULL    |  500.00 |
| NULL   | NULL    | 1320.00 |
+--------+---------+---------+
9 rows in set

When you do not want it

WITH ROLLUP makes the output less “flat”. If another program reads the result, the extra rows with NULL can be a nuisance. In an application, it is often cleaner to calculate the totals in code, or to run a second query without grouping.

The old way: UNION ALL

Before ROLLUP, you wrote a separate query for each level and joined them with UNION ALL (UNION). It still works, and it is portable, but it is longer and reads the table several times.

Differences from other databases

Standard SQL has GROUP BY ROLLUP(a, b), and also CUBE and GROUPING SETS. MySQL supports only the WITH ROLLUP form (and, since 8.0.12, the GROUPING() function). There is no CUBE and no GROUPING SETS. For other combinations of subtotals, use UNION ALL.

info

Version note

Before MySQL 8.0.12, you could not mix ORDER BY with WITH ROLLUP, and GROUPING() did not exist. Check your version if the examples above fail.