SQL / AGGREGATION WITH GROUP BY
Rollups and grouping sets for subtotals
Produce subtotal and grand-total rows inside a single GROUP BY using ROLLUP, CUBE and GROUPING SETS, and label them safely with GROUPING().
What you will learn
- Read GROUP BY ROLLUP (a, b) as the levels (a, b), (a) and () stacked in one result.
- Pick ROLLUP for prefix subtotals, CUBE for every subset, GROUPING SETS for exact ones.
- Use GROUPING(col) = 1, never COALESCE, to tell subtotal NULLs from data NULLs.
- Sort a rollup report with ORDER BY GROUPING(a), a, GROUPING(b), b.
Understanding Rollups and grouping sets for subtotals
A plain GROUP BY answers one question at a time: one row per distinct combination of the grouping columns. A report usually wants more than that at once, namely the detail rows, a subtotal per region, and a bottom line. ROLLUP, CUBE and GROUPING SETS compute several grouping levels in one pass, and the result behaves like the outputs of several separate GROUP BY queries appended together, with NULL filled into the columns a given level did not group by.
ROLLUP (region, product) is hierarchical: it takes the prefixes of the column list, giving (region, product), then (region), then (). So n columns produce n+1 levels, and the column order decides which subtotals exist. CUBE (region, product) instead produces every subset, 2^n levels, so it adds the per-product totals as well. GROUPING SETS is the raw form the other two expand into: you list exactly the levels you want, and () denotes the grand total.
The NULLs in subtotal rows are placeholders rather than data, which becomes a real problem as soon as the grouped column can itself be NULL. GROUPING(col) exists for that: it returns 1 when the column was collapsed for that row and 0 when the value came from the data, so it is the only reliable way to label subtotal rows and to sort them into position. Keep in mind too that every level re-aggregates the base rows, so an AVG grand total is the average of all rows, not the average of the subtotal averages.
Placeholder
WITH sales(region, product, amount) AS (
VALUES ('East', 'chair', 100),
('East', 'desk', 250),
('West', 'chair', 300),
('West', 'desk', 150),
('West', 'lamp', 50)
)
SELECT region, product, SUM(amount) AS total
FROM sales
GROUP BY ROLLUP (region, product)
ORDER BY region NULLS LAST, product NULLS LAST;ROLLUP, CUBE and GROUPING SETS run several grouping levels in one query and stack their rows, using NULL to mark the columns a row is not grouped by.
Worked examples
Separating subtotal NULLs from data NULLs
GROUPING(product) marks the rows ROLLUP invented, even when the data contains a genuine NULL product.
WITH sales(region, product, amount) AS (
VALUES ('East', 'chair', 100),
('East', NULL, 40),
('West', 'chair', 300)
)
SELECT region,
product,
GROUPING(product) AS is_subtotal,
SUM(amount) AS total
FROM sales
GROUP BY ROLLUP (region, product)
ORDER BY GROUPING(region), region, GROUPING(product), product;Example explained
Line 1Rows 2 and 3 both print an empty product, but row 2 is the real NULL-product group (40) and row 3 is the East subtotal (140).
Line 2GROUPING(product) returns 1 only when the grouping set collapsed that column, so it cannot be confused by NULLs stored in the table.
Line 3The final row has region and product both collapsed; that is the () level and its 440 covers every base row.
Line 4ORDER BY GROUPING(region), region, GROUPING(product), product keeps each subtotal directly beneath its detail rows and pushes the grand total to the bottom.
Two independent breakdowns with GROUPING SETS
Totals per region and totals per product in one result, without the region-by-product detail rows.
WITH sales(region, product, amount) AS (
VALUES ('East', 'chair', 100),
('East', 'desk', 250),
('West', 'chair', 300)
)
SELECT region, product, SUM(amount) AS total
FROM sales
GROUP BY GROUPING SETS ((region), (product), ())
ORDER BY region NULLS LAST, product NULLS LAST;Example explained
Line 1The grouping set list names three levels, so the query is really three GROUP BYs stacked; ROLLUP cannot express this because (product) is not a prefix of (region, product).
Line 2No output row has both a region and a product, since (region, product) was left out of the list.
Line 3chair totals 400 across both regions, so each base row is counted once per level: 350 + 300 and 400 + 250 both add up to 650.
Line 4() is the empty grouping set and produces the single 650 row.
CUBE for every combination
CUBE expands to all subsets of the grouping columns, so column order no longer matters.
WITH sales(region, product, amount) AS (
VALUES ('East', 'chair', 100),
('West', 'chair', 300),
('West', 'desk', 150)
)
SELECT region, product, SUM(amount) AS total
FROM sales
GROUP BY CUBE (region, product)
ORDER BY region NULLS LAST, product NULLS LAST;Example explained
Line 1CUBE (region, product) is shorthand for (region, product), (region), (product) and (), one more level than ROLLUP over the same columns.
Line 2The East subtotal of 100 repeats its only detail row; equal detail and subtotal values are expected when a group has a single row.
Line 3CUBE (product, region) would return exactly the same rows, because subsets have no order; swapping ROLLUP's columns changes the result.
Line 4Level count grows as 2^n, so CUBE over four columns already produces 16 grouping sets.
Important notes
Each level re-aggregates the base rows, so with AVG or COUNT(DISTINCT) the total row will not equal any arithmetic combination of the subtotal rows.
Support differs: PostgreSQL, Oracle, SQL Server and DB2 accept ROLLUP, CUBE and GROUPING SETS; MySQL 8 offers only GROUP BY a, b WITH ROLLUP plus GROUPING(); SQLite has none of them.
Common mistakes
Writing ROLLUP (product, region) when the report needs regional subtotals: you get one subtotal per product instead, and the per-region rows are simply missing.
Labelling subtotals with COALESCE(region, 'Total'): rows whose region is genuinely NULL in the data get the same label as the grand total, and readers count them twice.
Adding HAVING product IS NOT NULL to tidy the display: that predicate is false on every rolled-up row, so all the subtotals you just built are filtered out.
Try it yourself
Change, predict, then run
Run the main query, then replace ROLLUP (region, product) with GROUPING SETS ((region, product), (product), ()) and add GROUPING(region) AS g to the select list. Confirm you now get per-product totals across regions instead of per-region subtotals.
Open the SQL workspaceCheck your understanding
A table holds 3 regions and 4 products, with every combination present. How many rows does SELECT region, product, SUM(amount) ... GROUP BY ROLLUP (region, product) return?
- 12
- 13
- 16
- 20
Show answer
ROLLUP adds the (region) level and the () level to the detail level: 12 detail rows + 3 region subtotals + 1 grand total = 16. 20 is what CUBE would return, since it also adds the 4 per-product rows; 13 assumes only a grand total gets appended.