Subtotals and grand totals in one query: ROLLUP and GROUPING

Three grains in one report

A sales report wants revenue by city, a subtotal for each region, and a grand total at the bottom. That is three queries with three different GROUP BY clauses, and three scans of the table. Or it is one:

SELECT region, city, sum(amount) AS revenue
FROM sales
GROUP BY ROLLUP (region, city)
ORDER BY region, city;

ROLLUP (region, city) produces the piles for (region, city), then for (region), then for (), which is everything. The output stacks them. The subtotal row for a region has city set to NULL, and the grand total row has both columns NULL.

CUBE (region, channel) goes further and produces every combination: by region and channel, by region alone, by channel alone, and the total. GROUPING SETS ((region, city), (channel), ()) lets you name exactly which piles you want and skip the rest, which is what you reach for when CUBE would produce thirty subtotals nobody reads.

Postgres, DuckDB, SQL Server, Oracle and BigQuery support all three. MySQL has GROUP BY region, city WITH ROLLUP and nothing else. SQLite has none of them, and the fallback is UNION ALL of the separate grouped queries, which is more typing and the same result.

The NULL that is not a NULL

The subtotal rows use NULL to mean “all cities”. That collides head-on with a real NULL: a sale whose city was never recorded also produces a row with city NULL, at the detail grain. In the output the two rows look identical, and a report that labels every NULL city as “Total” has just relabelled its missing data as a subtotal.

GROUPING() tells them apart. It returns 1 when the column was rolled up in that row, and 0 when the row is at the detail grain and the NULL is genuine:

SELECT
  CASE WHEN GROUPING(region) = 1 THEN 'All regions' ELSE region END AS region,
  CASE WHEN GROUPING(city)   = 1 THEN 'All cities'  ELSE city   END AS city,
  sum(amount) AS revenue
FROM sales
GROUP BY ROLLUP (region, city);

Now a real unknown city stays NULL and a subtotal is labelled as one. In a report with any nullable grouping column, GROUPING() is not optional.

What the output is not

The result of a ROLLUP is not a table at one grain. It is three tables stacked, and any arithmetic across the whole result is wrong. SELECT sum(revenue) FROM (...rollup...) counts every sale three times: once in its city, once in its region, once in the total. Anybody who exports the result to a spreadsheet and drags a SUM over the column makes this mistake by reflex.

So a ROLLUP result is for reading, not for further computation. If a downstream step needs the numbers, give it the detail grain and let it compute its own subtotals, or give it the subtotal rows only, filtered with WHERE GROUPING(city) = 1.

Ordering the rows

The natural output order puts subtotals wherever the engine’s hash table happened to leave them. To get the layout people expect, city rows followed by their region’s subtotal and then the grand total last, sort on the grouping flags:

ORDER BY GROUPING(region), region, GROUPING(city), city

Detail rows have flag 0 and sort first within each region; the subtotal has flag 1 and follows; the grand total has both flags set and comes last.

When to use it

ROLLUP earns its place in reports that will be read as a document: a printed monthly summary, a PDF for a board, an exported sheet. It costs one scan instead of three and keeps the subtotals consistent with the detail by construction, because they are computed from the same filtered rows in the same statement. It does not belong in a query that feeds a chart or another query, where a single grain is what the consumer expects and a stacked one will be silently misread.

GROUPING SETS is the version to remember for the common request of “totals by region and, separately, totals by channel”: it produces exactly those two breakdowns from one scan without the full region-by-channel grid that CUBE would add in between.