Subtotals Without the Spreadsheet: GROUPING SETS, ROLLUP, and CUBE
One query that returns the detail rows and their subtotals and the grand total together — GROUPING SETS to name each grouping, ROLLUP for running subtotals, CUBE for every combination, and GROUPING() to tell a subtotal NULL from a real one. Run against PostgreSQL 18.
A plain GROUP BY genre, status gives you one row per combination and nothing else. But the report a human actually wants usually has more than that: the detail rows, and a subtotal per genre, and a grand total at the bottom. In a spreadsheet you’d add those by hand. The naive SQL approach is to run the query three times at three different grouping levels and stack the results with UNION ALL. That works, and it scans the table three times to do it.
GROUPING SETS collapses all of those into one query and one scan. ROLLUP and CUBE are shorthand for the two grouping patterns you reach for most. This chapter is the three of them, plus the one function you need to read their output without being fooled.
Several groupings in one query
GROUP BY GROUPING SETS (...) lets you list several groupings and get the union of all of them back. Each grouping is written as a parenthesized list of columns; () is the empty grouping, which is the grand total. Here is revenue by genre, by status, and overall, in a single statement. I’ve filtered to two genres to keep the output readable:
SELECT b.genre, o.status,
round(sum(oi.quantity * oi.unit_price), 2) AS revenue
FROM order_items oi
JOIN books b ON b.book_id = oi.book_id
JOIN orders o ON o.order_id = oi.order_id
WHERE b.genre IN ('Sci-Fi','Fiction')
GROUP BY GROUPING SETS ((b.genre), (o.status), ())
ORDER BY b.genre, o.status; genre | status | revenue
---------+-----------+------------
Fiction | | 1390433.23
Sci-Fi | | 1567345.25
| cancelled | 255799.03
| delivered | 1226525.20
| placed | 481248.71
| returned | 497216.39
| shipped | 496989.15
| | 2957778.48
(8 rows)Read the groupings off the query. (b.genre) produced the first two rows: revenue per genre, with status left NULL because status wasn’t part of that grouping. (o.status) produced the five status rows, with genre NULL. And () produced the last row, the grand total, with both columns NULL. One pass over the join, three grouping levels, stacked into one result. That NULL-means-”not grouped-by-here” convention is the whole reading trick, and we’ll deal with its one hazard at the end.
ROLLUP: running subtotals down a hierarchy
ROLLUP (a, b) is shorthand for a specific, common family of grouping sets: the full detail, then subtotals rolling up the list from right to left, then the grand total. ROLLUP (genre, status) expands to GROUPING SETS ((genre, status), (genre), ()). It’s the right tool when your columns form a hierarchy — genre contains statuses — and you want a subtotal at each level:
SELECT b.genre, o.status,
round(sum(oi.quantity * oi.unit_price), 2) AS revenue
FROM order_items oi
JOIN books b ON b.book_id = oi.book_id
JOIN orders o ON o.order_id = oi.order_id
WHERE b.genre IN ('Sci-Fi','Fiction')
GROUP BY ROLLUP (b.genre, o.status)
ORDER BY b.genre, o.status; genre | status | revenue
---------+-----------+------------
Fiction | cancelled | 124512.62
Fiction | delivered | 580680.54
Fiction | placed | 223735.49
Fiction | returned | 228651.54
Fiction | shipped | 232853.04
Fiction | | 1390433.23
Sci-Fi | cancelled | 131286.41
Sci-Fi | delivered | 645844.66
Sci-Fi | placed | 257513.22
Sci-Fi | returned | 268564.85
Sci-Fi | shipped | 264136.11
Sci-Fi | | 1567345.25
| | 2957778.48
(13 rows)Ten detail rows, and after each genre’s block a subtotal row where status is NULL, and one grand-total row at the bottom where both are NULL. This is exactly the layout of a financial report: line items, a subtotal per section, a total at the end. Notice ROLLUP does not give you a subtotal per status across genres — rolling up is directional. If you want the status subtotals too, that’s what CUBE is for.
CUBE: every combination at once
CUBE (a, b) gives the full cross-product of groupings: detail, subtotals on a, subtotals on b, and the grand total. CUBE (genre, status) expands to GROUPING SETS ((genre, status), (genre), (status), ()) — everything ROLLUP gives you, plus the per-status subtotals it skips:
SELECT b.genre, o.status,
round(sum(oi.quantity * oi.unit_price), 2) AS revenue
FROM order_items oi
JOIN books b ON b.book_id = oi.book_id
JOIN orders o ON o.order_id = oi.order_id
WHERE b.genre IN ('Sci-Fi','Fiction')
GROUP BY CUBE (b.genre, o.status)
ORDER BY b.genre NULLS LAST, o.status NULLS LAST; genre | status | revenue
---------+-----------+------------
Fiction | cancelled | 124512.62
Fiction | delivered | 580680.54
...
Fiction | | 1390433.23
Sci-Fi | cancelled | 131286.41
...
Sci-Fi | | 1567345.25
| cancelled | 255799.03
| delivered | 1226525.20
| placed | 481248.71
| returned | 497216.39
| shipped | 496989.15
| | 2957778.48
(18 rows)Eighteen rows: the 10 detail combinations, 2 genre subtotals, 5 status subtotals, and the grand total. The row count grows fast — CUBE over n columns produces 2ⁿ grouping sets — so reach for it only when you genuinely want the full matrix. When your columns are a hierarchy, ROLLUP is both cheaper and more honest about intent.
GROUPING(): which NULLs are real?
Here’s the hazard. A subtotal row marks its rolled-up columns with NULL. But a real NULL in your data — a book with no genre, an order with no status — would land in a group that also shows NULL in that column. From the output alone, a genuine NULL and a subtotal marker look identical. You cannot tell them apart by eye, and neither can a downstream tool.
GROUPING(col) resolves it. It returns 1 when the column was rolled up (a subtotal marker) and 0 when the row carries a real grouped value (including a real NULL). Wrap it in CASE and your subtotals get readable labels instead of blank cells:
SELECT
CASE WHEN GROUPING(b.genre) = 1 THEN 'ALL GENRES' ELSE b.genre END AS genre,
CASE WHEN GROUPING(o.status) = 1 THEN 'ALL STATUS' ELSE o.status END AS status,
GROUPING(b.genre) AS g_genre,
GROUPING(o.status) AS g_status,
round(sum(oi.quantity * oi.unit_price), 2) AS revenue
FROM order_items oi
JOIN books b ON b.book_id = oi.book_id
JOIN orders o ON o.order_id = oi.order_id
WHERE b.genre IN ('Sci-Fi','Fiction')
GROUP BY ROLLUP (b.genre, o.status)
ORDER BY b.genre, o.status; genre | status | g_genre | g_status | revenue
------------+------------+---------+----------+------------
Fiction | cancelled | 0 | 0 | 124512.62
Fiction | delivered | 0 | 0 | 580680.54
Fiction | placed | 0 | 0 | 223735.49
Fiction | returned | 0 | 0 | 228651.54
Fiction | shipped | 0 | 0 | 232853.04
Fiction | ALL STATUS | 0 | 1 | 1390433.23
Sci-Fi | cancelled | 0 | 0 | 131286.41
...
Sci-Fi | ALL STATUS | 0 | 1 | 1567345.25
ALL GENRES | ALL STATUS | 1 | 1 | 2957778.48
(13 rows)The g_status column reads 0 on detail rows and 1 on the genre subtotals — that’s the machine-readable flag. GROUPING() is also the clean way to ORDER BY so subtotals sort to the bottom of their group rather than wherever a NULL happens to fall. And it’s the way to filter (HAVING GROUPING(status) = 1) if you want only the subtotal rows. Whenever your grouped columns can hold real NULLs, label your subtotals with GROUPING(). Trusting a bare NULL to mean “subtotal” is how a report quietly double-reports a genuine gap.
One note on portability
This is a place where databases diverge. GROUPING SETS, ROLLUP, and CUBE are standard SQL, and Postgres, SQL Server, and Oracle all have the full set. MySQL has only WITH ROLLUP — appended to GROUP BY, not written as a function — and no GROUPING SETS or CUBE at all, so a genre-and-status matrix there means the old UNION ALL of separate queries. SQLite has none of them. If your SQL has to run on MySQL, ROLLUP is the one pattern that ports, and even then the syntax differs.
Final thoughts
Subtotals and grand totals are a reporting staple, and SQL builds them into GROUP BY rather than making you stack queries. GROUPING SETS names each grouping level explicitly; ROLLUP is the hierarchical subtotals-and-total shorthand; CUBE is every combination, at 2ⁿ rows. All three mark their rolled-up columns with NULL, and GROUPING() is what keeps those markers from being confused with real NULLs in your data. One scan, every level of summary, correctly labeled. Next we take the same conditional-aggregation idea and turn categories sideways, so each one becomes its own column.
Comments