Gowtham Potureddi

Posted on Jul 12

SQL Grouping Sets, ROLLUP & CUBE: Multi-Dimensional Aggregation Deep Dive

#python #sql #dataengineering #interview

sql grouping sets rollup cube is the primitive every finance dashboard, product analytics rollup, marketing spend cross-tab, and cohort P&L eventually needs — and it is the primitive most engineers work around with painful UNION ALL towers because they never learned the modern ANSI syntax that turns "detail rows + subtotals + grand total" into a single scan of the fact table. The gap between "I know GROUP BY" and "I can produce a P&L with region + country + city subtotals and a grand total in a single query" is exactly the gap between a mid-level SQL candidate and a senior one. This guide is the honest tour of what actually happens when you write GROUP BY GROUPING SETS (...), ROLLUP(...), or CUBE(...) — how the planner turns each into a single-scan multi-aggregation, why the GROUPING() function is the trick that lets you tell a data-NULL apart from a subtotal-NULL, and how the eight engines you'll encounter in 2026 all spell the same idea.

The tour walks the three ANSI primitives — the general-purpose GROUPING SETS ((a,b),(a),(b),()) that gives you any collection of GROUP BYs unioned into a single result, the hierarchical ROLLUP(a,b,c) that produces N+1 subtotals from leaf to grand-total following the argument order, and the combinatorial CUBE(a,b) that produces all 2^N cross-tab subtotals for full pivot-style output. Alongside them, the tour walks the GROUPING(col) and GROUPING_ID(...) functions that let you format subtotal rows correctly ("Total" instead of NULL), the SQL Server plan-shape variants (hash aggregate vs stream aggregate on ROLLUP), the Snowflake / BigQuery / Databricks warehouse cost stories (single scan vs N scans), and the MySQL WITH ROLLUP legacy that predated the ANSI spec by a decade. Every section ships a teaching block followed by a Solution-Tail interview answer — code, a step-by-step trace, an output table, then a concept-by-concept breakdown of why it works — so you leave with the two-line skeleton and the reason it wins.

When you want hands-on reps immediately after reading, drill the SQL aggregation practice library → for GROUP BY, GROUPING SETS, ROLLUP, and CUBE patterns, sharpen SQL optimization drills → for reading multi-aggregation plans, and layer the broader SQL practice surface → which contains 450+ DE-focused questions covering multi-dimensional aggregation and every adjacent pattern.

On this page

Why multi-dimensional aggregation matters in 2026
GROUPING SETS anatomy
ROLLUP for hierarchical subtotals
CUBE for full cross-tab aggregation
GROUPING() function + dialect matrix + patterns
Cheat sheet — GROUPING SETS / ROLLUP / CUBE recipe list
Frequently asked questions
Practice on PipeCode

1. Why multi-dimensional aggregation matters in 2026

The `multi-dimensional aggregation` mental model — why the naive UNION ALL of many GROUP BYs is quadratic, and what a single-scan aggregation buys you

The one-sentence invariant: when a report needs the same aggregate at multiple granularities — detail rows plus subtotals plus grand totals — the naive answer is to UNION ALL N independent GROUP BY queries and pay N scans of the fact table; GROUPING SETS, ROLLUP, and CUBE replace that with a single scan that produces every level in one pass. On a fact table of billions of rows, this is the difference between a 30-second dashboard and a 5-minute one.

Where multi-dimensional aggregation actually shows up.

Finance P&L rollups. Revenue by (region, country, city) plus (region, country) subtotals plus (region) subtotals plus grand total. Every finance report ships one of these; the CFO's dashboard wouldn't render without it.
Product analytics cohorts. Weekly retention by (signup_cohort, product) with cohort-only rollups and product-only rollups. GROUPING SETS is the natural fit.
Marketing spend attribution. Spend by (channel, campaign, adgroup) with per-channel and per-campaign subtotals. The classic marketing-ops report.
Warehouse metrics. Bytes scanned by (project, dataset, user) with all three combinations of subtotals. FinOps dashboards live on this.
Sales pivot tables. Sales by (region, product) with row totals, column totals, and grand total. CUBE is exactly this.
Executive dashboards. Single-page KPI grids with detail rows and rollup subtotals in the same table. GROUPING SETS lets you produce it in one query.
BI tool "totals row". Every Tableau, Power BI, Looker report with a "Grand Total" row at the bottom is either doing GROUPING SETS under the hood or issuing an extra query.

The N-way GROUP BY explosion.

Suppose you need three views: GROUP BY region, product, GROUP BY region, GROUP BY product. Naively, three separate SELECTs UNION ALL'd together — three scans of the fact table.
On a 10 B-row fact table, three scans is three times the cost. At $5/TB on BigQuery, if each scan is 1 TB, that's $15 per report render.
The right answer — one query with GROUP BY GROUPING SETS ((region, product), (region), (product)). Single scan, single price.
The math — for N dimensions and K wanted grouping combinations, naive = K scans, GROUPING SETS = 1 scan. Ratio is K:1.
Where the win compounds. BI dashboards typically want 4–8 subtotal levels. Naive costs 4–8×; GROUPING SETS costs 1×.

What senior interviewers actually probe.

Do you know the three primitives? GROUPING SETS (arbitrary), ROLLUP (hierarchical), CUBE (all subsets). One-line difference: GROUPING SETS is manual, ROLLUP is directional, CUBE is combinatorial.
Do you know when to use each? Manual custom set → GROUPING SETS. Ordered hierarchy (region → country → city) → ROLLUP. Full cross-tab (region × product all combinations) → CUBE.
Do you know the GROUPING() function? Returns 1 if the column is aggregated at this row's grain, 0 if it's preserved. Lets you distinguish "NULL means the column was aggregated" from "NULL means missing data."
Do you know how to format subtotal labels? CASE WHEN GROUPING(region) = 1 THEN 'All Regions' ELSE region END AS region_label. Every P&L report ships this.
Do you know the cost model? GROUPING SETS is single-scan; SQL Server's optimiser converts to hash aggregate; Snowflake / BigQuery execute in one stage; on-demand cost is 1× a single GROUP BY.
Do you know when NOT to use CUBE? N > 5 dimensions → 32+ grouping sets → often more subtotals than you need. Prefer explicit GROUPING SETS with the exact combinations wanted.

The reading discipline — bottom-up per grouping set.

Step 1 — identify the grouping sets. Each entry in GROUPING SETS (...) is one aggregation grain. Row count in output ≈ sum of distinct groups per set.
Step 2 — check GROUPING() flags in the SELECT. GROUPING(col) = 1 means "aggregated at this row's grain."
Step 3 — read subtotal labels. Every NULL is either a data-NULL (from the source column) or a subtotal-NULL (from the grouping). Use GROUPING() to disambiguate.
Step 4 — verify totals. The sum of subtotal rows should equal the grand total row.
Step 5 — check plan shape. On any modern engine, GROUPING SETS should produce a single-scan plan with an aggregate operator that outputs multiple grains.

Worked example — the naive UNION ALL vs GROUPING SETS

Detailed explanation. The archetype: you need revenue by (region, product), by (region), and grand total. Naive answer is three queries UNION ALL'd. Modern answer is one query with GROUPING SETS.

Question. Given a sales(region, product, revenue) table, write two versions of a report showing revenue by (region, product) + region-subtotal + grand-total. Compare plan shape and cost.

Input.

region	product	revenue
US	Widget	100
US	Gadget	50
EU	Widget	80
EU	Gadget	40

Code.

-- Naive: three queries, three scans, three unions
SELECT region, product, SUM(revenue) AS revenue
FROM sales
GROUP BY region, product
UNION ALL
SELECT region, NULL, SUM(revenue)
FROM sales
GROUP BY region
UNION ALL
SELECT NULL, NULL, SUM(revenue)
FROM sales;

-- Modern: one query, one scan, one plan
SELECT region, product, SUM(revenue) AS revenue
FROM sales
GROUP BY GROUPING SETS ((region, product), (region), ());

Step-by-step explanation.

Naive path — three independent SELECTs. Each is a separate GROUP BY, each requires a full scan of sales. Then the three result sets are unioned.
Modern path — one SELECT with three grouping sets. The planner sees the ask: aggregate at three grains. It scans sales once, computes all three aggregations in one pass, and returns them.
On a 1 B-row fact table with a SELECT SUM(revenue), the naive path reads ~24 GB three times; the modern path reads ~24 GB once. 3× cost saving.
On BigQuery on-demand at $5/TB, the naive at 1 TB per scan is $15 per report; the modern is $5.
Correctness — both produce the same result set. The modern is unambiguous about the intent.

Output.

region	product	revenue
US	Widget	100
US	Gadget	50
EU	Widget	80
EU	Gadget	40
US	NULL	150
EU	NULL	120
NULL	NULL	270

Rule of thumb. Any report with detail + subtotals + grand total should be a single GROUPING SETS query. If you're writing UNION ALL of N GROUP BYs, refactor.

Worked example — reading the GROUPING() flag on subtotal rows

Detailed explanation. The output above has NULLs in "subtotal" positions (product=NULL for a region-subtotal row). But NULL could also be a data-NULL (a row where product wasn't set). The GROUPING() function returns 1 when the column is aggregated (subtotal-NULL) and 0 when it's preserved (detail-NULL from data).

Question. Given the output from the previous example, add GROUPING() flags so you can distinguish subtotal rows from data rows. Then format the labels.

Input. Result from the GROUPING SETS query above.

Code.

SELECT
  CASE WHEN GROUPING(region)  = 1 THEN 'All Regions'  ELSE region  END AS region_label,
  CASE WHEN GROUPING(product) = 1 THEN 'All Products' ELSE product END AS product_label,
  SUM(revenue) AS revenue,
  GROUPING(region) AS is_region_agg,
  GROUPING(product) AS is_product_agg,
  GROUPING_ID(region, product) AS grouping_id
FROM sales
GROUP BY GROUPING SETS ((region, product), (region), ())
ORDER BY GROUPING_ID(region, product), region, product;

Step-by-step explanation.

GROUPING(region) — returns 1 when region is aggregated (subtotal), 0 when preserved.
GROUPING(product) — same for product.
GROUPING_ID(region, product) — bitmask: 00 = detail, 01 = region-only, 10 = product-only, 11 = grand total. Numeric: 0, 1, 2, 3.
CASE WHEN GROUPING(...) = 1 THEN '...' — replaces NULL with human-readable label.
ORDER BY GROUPING_ID(...) — sorts detail rows first, then subtotal rows, then grand total.

Output.

region_label	product_label	revenue	is_region_agg	is_product_agg	grouping_id
US	Widget	100	0	0	0
US	Gadget	50	0	0	0
EU	Widget	80	0	0	0
EU	Gadget	40	0	0	0
US	All Products	150	0	1	1
EU	All Products	120	0	1	1
All Regions	All Products	270	1	1	3

Rule of thumb. Any GROUPING SETS query in a BI tool needs GROUPING() to format subtotal labels. Never let raw NULLs leak into the final report.

Worked example — combining GROUPING SETS with a window function for percentages

Detailed explanation. A common finance dashboard pattern: revenue by (region, product) with subtotals, plus a "% of grand total" column. Combine GROUPING SETS with a window function that references the grand total row.

Question. Extend the previous example to include a pct_of_total column showing each row's revenue as a percentage of the grand total.

Input. Same sales data.

Code.

WITH agg AS (
  SELECT
    region,
    product,
    SUM(revenue) AS revenue,
    GROUPING_ID(region, product) AS grouping_id
  FROM sales
  GROUP BY GROUPING SETS ((region, product), (region), ())
)
SELECT
  CASE WHEN grouping_id IN (2, 3) THEN 'All Regions' ELSE region END AS region_label,
  CASE WHEN grouping_id IN (1, 3) THEN 'All Products' ELSE product END AS product_label,
  revenue,
  ROUND(
    100.0 * revenue / MAX(CASE WHEN grouping_id = 3 THEN revenue END) OVER (),
    2
  ) AS pct_of_total
FROM agg
ORDER BY grouping_id, region, product;

Step-by-step explanation.

The CTE agg produces one row per grouping set (detail + region-subtotal + grand-total).
MAX(CASE WHEN grouping_id = 3 THEN revenue END) OVER () — window over the whole result. Picks out the grand-total revenue.
revenue / <grand_total> — each row's percentage.
ORDER BY grouping_id — detail first (grouping_id=0), then region subtotals (=1), then grand total (=3).
Combining GROUPING SETS with window functions is a common senior pattern — the CTE materialises the multi-grain aggregate, the window picks the reference value.

Output.

region_label	product_label	revenue	pct_of_total
US	Widget	100	37.04
US	Gadget	50	18.52
EU	Widget	80	29.63
EU	Gadget	40	14.81
US	All Products	150	55.56
EU	All Products	120	44.44
All Regions	All Products	270	100.00

Rule of thumb. GROUPING SETS + window functions is the finance-dashboard combination — multi-grain totals plus per-row references. Learn this pattern; it shows up everywhere.

Common beginner mistakes

Writing N separate GROUP BY queries UNION ALL'd — always convert to GROUPING SETS.
Confusing data-NULL with subtotal-NULL — use GROUPING() to disambiguate.
Forgetting the empty grouping set () for grand total.
Assuming ROLLUP produces all 2^N combinations — it only produces N+1 (hierarchical).
Using CUBE with many dimensions — 2^N blows up fast.
Ordering results without GROUPING_ID — subtotal rows scatter into detail rows.

`sql grouping sets` interview question on refactoring UNION ALL to single-scan

A senior interviewer often opens with: "You inherit this SQL Server report that runs three separate GROUP BY queries UNION ALL'd, and it takes 45 seconds. Rewrite it to run in one query, explain the plan-shape difference, and predict the new wall clock."

Solution Using GROUPING SETS to collapse N scans into one

-- The original (three scans, 45 seconds)
SELECT region, country, SUM(revenue) AS revenue
FROM sales
GROUP BY region, country
UNION ALL
SELECT region, NULL, SUM(revenue)
FROM sales
GROUP BY region
UNION ALL
SELECT NULL, NULL, SUM(revenue)
FROM sales;

-- The refactor (one scan, ~15 seconds)
SELECT
  CASE WHEN GROUPING(region)  = 1 THEN 'All Regions'   ELSE region  END AS region_label,
  CASE WHEN GROUPING(country) = 1 THEN 'All Countries' ELSE country END AS country_label,
  SUM(revenue) AS revenue,
  GROUPING_ID(region, country) AS grouping_id
FROM sales
GROUP BY GROUPING SETS ((region, country), (region), ())
ORDER BY GROUPING_ID(region, country), region, country;

Step-by-step trace.

Step	Original (UNION ALL × 3)	Refactor (GROUPING SETS)
1	Query 1 scans sales (~1 B rows)	Single scan of sales (~1 B rows)
2	Query 2 scans sales again (~1 B rows)	Aggregate operator computes all three grains
3	Query 3 scans sales again (~1 B rows)	Output: detail + region-subtotal + grand-total rows
4	UNION ALL three result sets	ORDER BY grouping_id for report format
5	Total scans: 3, total wall clock ~45 s	Total scans: 1, total wall clock ~15 s
6	On-demand cost: 3 TB × $5 = $15	On-demand cost: 1 TB × $5 = $5

Output:

Metric	Original	Refactor
Full-table scans	3	1
Wall clock	~45 s	~15 s
On-demand cost (BigQuery-style)	3× base	1× base
Reads across engines (bytes scanned)	3×	1×
Correctness	Equivalent	Equivalent

Why this works — concept by concept:

Single-scan aggregation — the planner sees three requested grains and computes all three during a single pass over the fact table. Reduces I/O by exactly N (where N = number of grouping sets).
GROUPING_ID(region, country) for ordering — sorts detail rows before subtotals before grand total. Without it, subtotal rows scatter.
CASE WHEN GROUPING() = 1 THEN 'All ...' — formats subtotal labels to be readable. Every BI dashboard needs this.
Cost — Scan cost is proportional to fact-table size (N rows × row width). Reducing N scans to 1 = N× cost saving. On a 1 TB fact table, the win is dollars per report.
Ordering trade-off — ORDER BY on a large result requires a sort operator. Usually fine since output cardinality is small (~thousands of rows after aggregation).

SQL
Topic — aggregation
SQL aggregation drills

Practice →

SQL Topic — optimization SQL optimization drills

Practice →

2. GROUPING SETS anatomy

`sql grouping sets` — the general-purpose multi-grain aggregation primitive that expresses N GROUP BYs as one

The mental model in one line: GROUP BY GROUPING SETS ((a,b), (a), (b), ()) tells the planner to aggregate at four grains — full detail (a,b), a-marginal (a), b-marginal (b), and grand total () — and produce one row per group per set in a single scan; each grouping set is an independent GROUP BY unioned with the others, but executed with shared reads. Master GROUPING SETS and you can build any multi-dim report as a single query.

Slot 1 — the grammar.

GROUP BY GROUPING SETS (set_list) — the syntax. Each entry is a parenthesised list of column expressions.
A grouping set is a parenthesised list. (a, b) means "group by both a and b." (a) means "group by a only, aggregate over b." () — the empty set — means "group by nothing, aggregate over all rows" (grand total).
Multiple sets separated by commas. GROUPING SETS ((a, b), (a), (b), ()) = four sets.
Each set produces its own partition of output rows. For each distinct value combination in the set, one output row. Aggregates apply per-partition.
Result is the UNION ALL of per-set results. But executed as a single scan, not N.
Column list in SELECT. Every column referenced in ANY grouping set must appear in the SELECT (or be aggregated). If a column appears in some sets but not others, the output has NULL in the rows where it's not part of the set.

Slot 2 — the empty grouping set () for grand total.

() = aggregate over all rows. Produces one output row with NULL for every dimension column and totals for every aggregate.
Reserved for grand total. Common at the bottom of P&L reports and dashboard totals.
Order-insensitive. ((a,b), ()) = ((a,b), ()) — the empty set is a distinct grouping regardless of position.
Detected by GROUPING(). For the grand-total row, GROUPING(a) = 1 and GROUPING(b) = 1 — every dimension is aggregated.
Cheap. The grand total is O(N) for the scan plus O(1) for the aggregate — negligible.

Slot 3 — the "detail" grouping set.

(a, b, c, ..., all_dims) — the finest grain. Effectively equivalent to a plain GROUP BY a, b, c.
Output row count = distinct combinations of (a, b, c).
Cost — the same as a plain GROUP BY. The bulk of the compute.
Every real report includes it. BI dashboards want detail + subtotals; the detail set is the base grain.
Combining with fact-table filters. WHERE clauses apply before GROUPING SETS. Filter down the fact table first, then aggregate.

Slot 4 — marginal grouping sets.

(a) — the a-marginal. Aggregate over all rows for each distinct value of a. Column b is NULL in output.
(b) — the b-marginal. Aggregate over all rows for each distinct value of b. Column a is NULL in output.
(a, c) — a partial detail. Aggregate over b for each distinct (a, c).
Purpose — subtotals by a dimension while ignoring others. "Revenue by region" ignoring product.
Output row count — for each distinct value in the set, one row. Usually small.

Slot 5 — the ordering rules within a set.

Column order in (a, b) is irrelevant. (a, b) = (b, a) — the SET of columns is what matters.
Duplicate columns are legal but redundant. (a, a) = (a).
Nested sets are not allowed. ((a, b)) is one set of two columns, not a nested "set of sets."
Expressions allowed. GROUPING SETS ((EXTRACT(YEAR FROM ts), region), (region), ()). Compound expressions work.

Slot 6 — GROUPING SETS with WHERE, HAVING, and ORDER BY.

WHERE fires before GROUPING SETS. Filters the fact table pre-aggregation. Same as with plain GROUP BY.
HAVING fires after GROUPING SETS. Filters aggregated rows. HAVING SUM(revenue) > 1000000 scopes each grouping set's output.
ORDER BY fires after aggregation. Can reference the aggregate expressions. Use GROUPING_ID() to sort grouping-set-first, then dimensions.
LIMIT last. Standard SQL evaluation order.

Slot 7 — the plan shape (single scan).

Postgres. Uses MixedAggregate or GroupAggregate node with multiple output grains. One scan of the fact table.
SQL Server. Uses Stream Aggregate or Hash Aggregate with multiple grouping expressions. One scan.
Snowflake. Single-stage aggregate with multi-key output. One scan.
BigQuery. Single-stage compute with multi-grain output. One scan; bytes billed = one full scan.
Databricks. Photon-accelerated single-stage aggregate. One scan.
Cost. ~1× a single GROUP BY; N× cheaper than N UNION ALL'd queries.

Slot 8 — GROUPING SETS with complex source queries.

CTEs are legal. Compute a materialised source in a CTE, then GROUP BY GROUPING SETS on it.
Subqueries in FROM are legal. Same story.
Joins are legal. JOIN a fact and dim, then GROUPING SETS the result.
View sources are legal. Views act as inline expressions.
Streaming sources — engine-specific. Some engines forbid GROUPING SETS on stream inputs.

Slot 9 — semantic edge cases.

NULL columns in the input. A row with region = NULL in the data still gets aggregated in the (region, product) set — its region is NULL and joins with other NULL rows.
GROUPING() still works. For the data-NULL row, GROUPING(region) = 0 — the column is preserved, just happens to be NULL.
Distinguishing data-NULL from subtotal-NULL — GROUPING() returns 0 for data-NULL, 1 for subtotal-NULL. This is the whole point of GROUPING().
NULL handling per engine. All engines behave the same way — the data-NULL is treated as any other value in the grouping set.

Common beginner mistakes

Writing GROUP BY GROUPING SETS (a, b, ()) — meant GROUP BY GROUPING SETS ((a), (b), ()). Parentheses matter.
Forgetting the outer GROUPING SETS(...) wrapper.
Not including the empty set () when a grand total is needed.
Reading NULL in output as data-NULL when it's a subtotal-NULL.
Not using GROUPING() to disambiguate — outputs look identical to data.
Applying WHERE to filter subtotals — WHERE fires before aggregation; use HAVING instead.

Worked example — the four-set GROUPING SETS with detail, two marginals, and grand total

Detailed explanation. A canonical BI report: revenue by (region, product), by region only, by product only, and grand total. Four grouping sets in one query.

Question. Given a sales(region, product, revenue) table, write a GROUPING SETS query producing all four grains.

Input.

region	product	revenue
US	Widget	100
US	Gadget	50
EU	Widget	80
EU	Gadget	40

Code.

SELECT
  region,
  product,
  SUM(revenue) AS revenue,
  GROUPING_ID(region, product) AS grouping_id
FROM sales
GROUP BY GROUPING SETS (
  (region, product),
  (region),
  (product),
  ()
)
ORDER BY GROUPING_ID(region, product), region, product;

Step-by-step explanation.

(region, product) — detail. Four rows: (US, Widget, 100), (US, Gadget, 50), (EU, Widget, 80), (EU, Gadget, 40).
(region) — region marginal. Two rows: (US, NULL, 150), (EU, NULL, 120).
(product) — product marginal. Two rows: (NULL, Widget, 180), (NULL, Gadget, 90).
() — grand total. One row: (NULL, NULL, 270).
Total output = 4 + 2 + 2 + 1 = 9 rows. Single scan of sales (4 rows), aggregate across all four grains.

Output.

region	product	revenue	grouping_id
US	Widget	100	0
US	Gadget	50	0
EU	Widget	80	0
EU	Gadget	40	0
US	NULL	150	1
EU	NULL	120	1
NULL	Widget	180	2
NULL	Gadget	90	2
NULL	NULL	270	3

Rule of thumb. Four grouping sets — detail, two marginals, grand total — is the canonical BI report shape. Almost every executive dashboard has one.

Worked example — GROUPING SETS with a HAVING filter

Detailed explanation. You want the report but only for regions with revenue > 100. HAVING fires after aggregation and applies to each grouping set independently.

Question. Modify the four-set query to only include region rows where the region's total revenue exceeds 100.

Input. Same sales data.

Code.

SELECT
  region,
  product,
  SUM(revenue) AS revenue,
  GROUPING_ID(region, product) AS grouping_id
FROM sales
GROUP BY GROUPING SETS (
  (region, product),
  (region),
  ()
)
HAVING GROUPING(region) = 1 OR SUM(revenue) > 100
ORDER BY GROUPING_ID(region, product), region, product;

Step-by-step explanation.

HAVING applies per output row.
GROUPING(region) = 1 OR SUM(revenue) > 100 — keep rows where region is aggregated (subtotal / grand total) OR where the row's revenue exceeds 100.
Detail row (US, Gadget, 50) — GROUPING(region)=0, revenue=50 < 100. Filtered out.
Detail row (EU, Gadget, 40) — same. Filtered out.
All other rows pass. Region subtotals (150, 120) pass because they're subtotals. Grand total (270) passes.

Output.

region	product	revenue	grouping_id
US	Widget	100	0
EU	Widget	80	0
US	NULL	150	1
EU	NULL	120	1
NULL	NULL	270	3

Rule of thumb. HAVING fires per grouping-set row. Use GROUPING() in HAVING to scope filters to detail vs subtotal.

Worked example — GROUPING SETS with a join

Detailed explanation. Fact table joined to a dim table, then GROUPING SETS on the join result. Common for "revenue by region + subtotal, but include the region_name from dim_region."

Question. Given sales(region_id, product, revenue) and dim_region(region_id, region_name), write a GROUPING SETS query showing revenue by (region_name, product), by region_name, and grand total.

Input.

sales:
  region_id | product | revenue
  1         | Widget  | 100
  1         | Gadget  | 50
  2         | Widget  | 80

dim_region:
  region_id | region_name
  1         | US
  2         | EU

Code.

SELECT
  r.region_name,
  s.product,
  SUM(s.revenue) AS revenue,
  GROUPING_ID(r.region_name, s.product) AS grouping_id
FROM sales s
JOIN dim_region r ON r.region_id = s.region_id
GROUP BY GROUPING SETS (
  (r.region_name, s.product),
  (r.region_name),
  ()
)
ORDER BY GROUPING_ID(r.region_name, s.product), r.region_name, s.product;

Step-by-step explanation.

JOIN happens first. sales × dim_region on region_id.
Post-join rows: (US, Widget, 100), (US, Gadget, 50), (EU, Widget, 80).
GROUPING SETS aggregates the joined result across three grains.
Detail set: 3 rows. Region marginal: 2 rows. Grand total: 1 row.
Total 6 rows in output.

Output.

region_name	product	revenue	grouping_id
US	Widget	100	0
US	Gadget	50	0
EU	Widget	80	0
US	NULL	150	1
EU	NULL	80	1
NULL	NULL	230	3

Rule of thumb. Join first, then GROUPING SETS on the result. The planner handles both in a single pipeline.

`sql grouping sets` interview question on building a P&L rollup report

A senior interviewer often asks: "Design a P&L report on sales(region, country, city, revenue, cost) that shows revenue, cost, and profit at four grains: (region, country, city), (region, country), (region), and grand total. Include subtotal labels and % of grand total. Explain why you chose GROUPING SETS over ROLLUP."

Solution Using GROUPING SETS with formatted labels + window for % of total

WITH agg AS (
  SELECT
    region,
    country,
    city,
    SUM(revenue) AS revenue,
    SUM(cost) AS cost,
    SUM(revenue - cost) AS profit,
    GROUPING_ID(region, country, city) AS grouping_id
  FROM sales
  GROUP BY GROUPING SETS (
    (region, country, city),
    (region, country),
    (region),
    ()
  )
)
SELECT
  CASE WHEN grouping_id >= 4 THEN 'All Regions' ELSE region END AS region_label,
  CASE WHEN grouping_id IN (2, 3, 6, 7) THEN 'All Countries' ELSE country END AS country_label,
  CASE WHEN grouping_id IN (1, 3, 5, 7) THEN 'All Cities' ELSE city END AS city_label,
  revenue,
  cost,
  profit,
  ROUND(
    100.0 * revenue / MAX(CASE WHEN grouping_id = 7 THEN revenue END) OVER (),
    2
  ) AS pct_of_total_revenue,
  grouping_id
FROM agg
ORDER BY grouping_id, region, country, city;

Step-by-step trace.

Grouping set	grouping_id	Row count	Label
(region, country, city)	0	~N distinct cities	Detail
(region, country)	1	~M distinct countries	City-subtotal
(region)	3	~R distinct regions	Country-subtotal
()	7	1	Grand total

Note: grouping_id bits correspond to (region, country, city) in that order — bit 0 is city (rightmost), bit 2 is region (leftmost). So grouping_id = 1 = binary 001 = city aggregated → the (region, country) grouping.

Output:

region_label	country_label	city_label	revenue	cost	profit	pct_of_total_revenue
US	US	NYC	100	40	60	25.00
US	US	SF	120	50	70	30.00
EU	UK	London	80	30	50	20.00
...	...	...	...	...	...	...
US	US	All Cities	220	90	130	55.00
EU	UK	All Cities	80	30	50	20.00
US	All Countries	All Cities	220	90	130	55.00
All Regions	All Countries	All Cities	400	160	240	100.00

Why this works — concept by concept:

Four explicit grouping sets — the P&L needs exactly these four grains. GROUPING SETS lets me name them explicitly, unlike ROLLUP which would produce them as a hierarchy but with the same result.
Why GROUPING SETS over ROLLUP — ROLLUP(region, country, city) produces exactly these four grains too. The difference is intent expression — GROUPING SETS is explicit about what you want; ROLLUP is directional and implies hierarchy. Use ROLLUP when the hierarchy is obvious; GROUPING SETS when the sets aren't sequential.
GROUPING_ID bitmask — 3 bits for 3 dimensions. Each bit is 1 if that dimension is aggregated. Sort ascending shows detail first, subtotals in order, grand total last.
Window function for pct — MAX(CASE WHEN grouping_id = 7 THEN revenue END) OVER () picks the grand-total revenue and broadcasts it to all rows.
Label formatting — the CASE expressions replace subtotal-NULLs with human-readable labels using the grouping_id bits.
Cost — one scan of sales. On a 10 M-row table, ~50 ms on modern hardware. Equivalent naive UNION-ALL would be 4 scans = ~200 ms + union merge cost.

SQL
Topic — aggregation
SQL aggregation drills

Practice →

SQL Topic — window functions Window function drills

Practice →

3. ROLLUP for hierarchical subtotals

`sql rollup` — the directional, hierarchical subtotal primitive that produces N+1 grouping sets from N dimensions

The mental model in one line: ROLLUP(a, b, c) is syntactic sugar for GROUPING SETS ((a,b,c), (a,b), (a), ()) — it walks a directional hierarchy from finest to coarsest, dropping the rightmost dimension at each step, and always ends with the empty set (grand total); use it when your dimensions form a natural hierarchy (region → country → city, year → quarter → month, department → team → individual).

Slot 1 — the grammar.

GROUP BY ROLLUP (a, b, c) — expands to GROUPING SETS ((a, b, c), (a, b), (a), ()).
N dimensions produce N+1 grouping sets. Detail + N-1 hierarchical subtotals + grand total.
Directional. The order of arguments matters. ROLLUP(region, country, city) ≠ ROLLUP(city, country, region).
The rightmost dimension is dropped first. ROLLUP(a, b, c) = detail (abc), then drop c: (ab), then drop b: (a), then drop a: ().
Composable. GROUP BY ROLLUP(a, b), c = detail(a,b,c) + subtotal(a,c) + (c) + (). ROLLUP can nest within a broader GROUP BY.
MySQL legacy. MySQL used GROUP BY a, b WITH ROLLUP since 4.1 (2004). The ANSI ROLLUP was added in MySQL 8.0.28 (2022). Both work in modern MySQL; the ANSI spelling is preferred.

Slot 2 — the N+1 fanout math.

N=1 — ROLLUP(a) = (a), () = 2 sets.
N=2 — ROLLUP(a, b) = (a, b), (a), () = 3 sets.
N=3 — ROLLUP(a, b, c) = (a, b, c), (a, b), (a), () = 4 sets.
N=4 — 5 sets. And so on — always N+1.
Compare to CUBE. N=3 → ROLLUP = 4 sets, CUBE = 2^3 = 8 sets. ROLLUP is a strict subset.
When to prefer ROLLUP. When dimensions form a hierarchy (region contains country contains city) and you only want the hierarchical subtotals, not all combinations.

Slot 3 — directional hierarchy examples.

Region → country → city. ROLLUP(region, country, city). Produces detail + country-subtotal + region-subtotal + grand total. Natural for geographic P&L.
Year → quarter → month → day. ROLLUP(year, quarter, month, day). Time-series drill-down. Common for finance close reports.
Department → team → individual. ROLLUP(department, team, individual). HR / expense report. Individual gets the finest, team subtotal, department subtotal, grand total.
Category → subcategory → product. ROLLUP(category, subcategory, product). Product-taxonomy report.

Slot 4 — ROLLUP vs the equivalent GROUPING SETS.

Explicit ROLLUP. GROUP BY ROLLUP(a, b, c) — 4 sets, hierarchical, directional.
Equivalent GROUPING SETS. GROUP BY GROUPING SETS ((a, b, c), (a, b), (a), ()). Same result.
Why prefer ROLLUP — shorter to write, conveys hierarchical intent, engine-side plan may be optimised for the sequential drop pattern.
Why prefer GROUPING SETS — when you want non-hierarchical combinations (e.g., detail + only (region, city) + grand total), you can't express that with ROLLUP.
Common trick. ROLLUP(a, b, c) + additional custom sets via GROUPING SETS, e.g., GROUP BY ROLLUP(a, b, c), GROUPING SETS ((d), ()) combines the two.

Slot 5 — MySQL WITH ROLLUP legacy.

GROUP BY a, b WITH ROLLUP — the pre-ANSI MySQL spelling. Same semantics as GROUP BY ROLLUP(a, b).
Postgres, SQL Server, Oracle, warehouses. All support ANSI ROLLUP since forever. Only MySQL had the WITH ROLLUP idiom.
MySQL 8.0.28+. ANSI ROLLUP supported natively. Prefer it in new code for portability.
MySQL restriction pre-8.0.28. WITH ROLLUP couldn't be combined with ORDER BY on the same statement (except via an outer query). Fixed in 8.0.28.

Slot 6 — ROLLUP with WHERE, HAVING, ORDER BY.

WHERE fires before ROLLUP. Standard. Filters the fact table pre-aggregation.
HAVING fires after ROLLUP. Filters aggregated rows. HAVING SUM(revenue) > 10000 scopes each grouping set.
ORDER BY. Sort on the output. Use GROUPING_ID() to sort detail first, then subtotals, then grand total.
LIMIT. After all aggregation.

Slot 7 — the plan shape.

Postgres. Uses MixedAggregate (PG 10+). One pass, N+1 grains produced.
SQL Server. Stream Aggregate when input is pre-sorted on the ROLLUP key; Hash Aggregate otherwise. Both single-scan.
Snowflake. Single-stage aggregate with multiple output grains.
BigQuery. Single-stage. Bytes billed = one scan.
Databricks. Single-stage. Photon accelerates.
Cost. ~1× a single GROUP BY at detail grain. Subtotals are cheap because they operate on the already-aggregated stream.

Slot 8 — composable ROLLUP.

Nested. GROUP BY ROLLUP(a), b, c — combines the ROLLUP(a) with plain GROUP BY on b, c. Grouping sets are: (a, b, c) + (b, c).
Multiple ROLLUPs. GROUP BY ROLLUP(a, b), ROLLUP(c, d) — Cartesian product of the two ROLLUP fanouts. Rarely needed but legal.
Mixed with CUBE. GROUP BY ROLLUP(a, b), CUBE(c, d) — mix hierarchical + combinatorial. Advanced pattern.

Slot 9 — interview probes on ROLLUP.

"When would you use ROLLUP vs GROUPING SETS?" — ROLLUP for directional hierarchies; GROUPING SETS for arbitrary sets.
"How many grouping sets does ROLLUP(a, b, c) produce?" — N+1 = 4.
"What's WITH ROLLUP in MySQL?" — the legacy pre-ANSI spelling; equivalent to ROLLUP(a, b).
"Can you combine ROLLUP with a regular GROUP BY column?" — Yes, via composition. GROUP BY ROLLUP(a, b), c includes c in every set.
"Why does the plan look like a single aggregate?" — the planner produces multiple grain outputs from a single scan.

Common beginner mistakes

Assuming ROLLUP produces all 2^N subsets — it produces only N+1 (hierarchical).
Reversing the argument order — hierarchy direction matters.
Forgetting the empty set is included — no need to add () explicitly to ROLLUP.
Confusing ROLLUP output with CUBE output — they differ when N > 1.
Applying WHERE to filter subtotals — WHERE is pre-aggregation; use HAVING.

Worked example — the ROLLUP(region, country, city) P&L rollup

Detailed explanation. The canonical geographic P&L. ROLLUP produces detail + city-subtotal + country-subtotal + region-subtotal + grand total in that order... wait, actually ROLLUP produces detail + drops city → (region, country) subtotal + drops country → (region) subtotal + drops region → grand total.

Question. Given sales(region, country, city, revenue), write a ROLLUP query producing the geographic P&L rollup.

Input.

region	country	city	revenue
US	US	NYC	100
US	US	SF	120
EU	UK	London	80
EU	UK	Manchester	40
EU	DE	Berlin	60

Code.

SELECT
  CASE WHEN GROUPING(region)  = 1 THEN 'All Regions'   ELSE region  END AS region_label,
  CASE WHEN GROUPING(country) = 1 THEN 'All Countries' ELSE country END AS country_label,
  CASE WHEN GROUPING(city)    = 1 THEN 'All Cities'    ELSE city    END AS city_label,
  SUM(revenue) AS revenue,
  GROUPING_ID(region, country, city) AS grouping_id
FROM sales
GROUP BY ROLLUP (region, country, city)
ORDER BY GROUPING_ID(region, country, city), region, country, city;

Step-by-step explanation.

ROLLUP(region, country, city) expands to 4 grouping sets: (region, country, city), (region, country), (region), ().
Detail set — 5 rows (one per city).
(region, country) — 3 rows: (US, US), (EU, UK), (EU, DE).
(region) — 2 rows: (US), (EU).
() — 1 row: grand total.
Total output rows = 5 + 3 + 2 + 1 = 11.

Output.

region_label	country_label	city_label	revenue	grouping_id
US	US	NYC	100	0
US	US	SF	120	0
EU	UK	London	80	0
EU	UK	Manchester	40	0
EU	DE	Berlin	60	0
US	US	All Cities	220	1
EU	UK	All Cities	120	1
EU	DE	All Cities	60	1
US	All Countries	All Cities	220	3
EU	All Countries	All Cities	180	3
All Regions	All Countries	All Cities	400	7

Rule of thumb. ROLLUP is the fastest way to write a hierarchical geographic (or temporal, or organizational) rollup. N+1 sets from N arguments. Argument order is the hierarchy.

Worked example — ROLLUP for a monthly-quarterly-yearly time series

Detailed explanation. A time-series P&L needs day → month → quarter → year drill-down. Use ROLLUP(year, quarter, month, day).

Question. Given a sales(sale_ts, revenue) table, write a ROLLUP query producing revenue by day, with month, quarter, year, and grand total subtotals.

Input.

-- sample data
CREATE TABLE sales AS
SELECT DATE '2026-01-15' AS sale_ts, 100 AS revenue UNION ALL
SELECT DATE '2026-01-20', 150 UNION ALL
SELECT DATE '2026-02-05', 80 UNION ALL
SELECT DATE '2026-04-10', 200;

Code.

SELECT
  EXTRACT(YEAR FROM sale_ts)    AS year,
  EXTRACT(QUARTER FROM sale_ts) AS quarter,
  EXTRACT(MONTH FROM sale_ts)   AS month,
  sale_ts                       AS day,
  SUM(revenue) AS revenue,
  GROUPING_ID(
    EXTRACT(YEAR FROM sale_ts),
    EXTRACT(QUARTER FROM sale_ts),
    EXTRACT(MONTH FROM sale_ts),
    sale_ts
  ) AS grouping_id
FROM sales
GROUP BY ROLLUP (
  EXTRACT(YEAR FROM sale_ts),
  EXTRACT(QUARTER FROM sale_ts),
  EXTRACT(MONTH FROM sale_ts),
  sale_ts
)
ORDER BY GROUPING_ID(...), year, quarter, month, day;

Step-by-step explanation.

4 dimensions → 5 grouping sets: (y, q, m, d), (y, q, m), (y, q), (y), ().
Detail — 4 rows (one per sale date).
Month subtotal — one per (year, quarter, month) — 3 rows.
Quarter subtotal — one per (year, quarter) — 2 rows.
Year subtotal — one per year — 1 row.
Grand total — 1 row.
Total = 4 + 3 + 2 + 1 + 1 = 11 rows.

Output.

year	quarter	month	day	revenue	grouping_id
2026	1	1	2026-01-15	100	0
2026	1	1	2026-01-20	150	0
2026	1	2	2026-02-05	80	0
2026	2	4	2026-04-10	200	0
2026	1	1	NULL	250	1
2026	1	2	NULL	80	1
2026	2	4	NULL	200	1
2026	1	NULL	NULL	330	3
2026	2	NULL	NULL	200	3
2026	NULL	NULL	NULL	530	7
NULL	NULL	NULL	NULL	530	15

Rule of thumb. Time-series ROLLUP is nearly universal. Every finance close report ships one. Argument order = drill-down direction.

Worked example — MySQL `WITH ROLLUP` legacy vs ANSI ROLLUP

Detailed explanation. MySQL predates the ANSI ROLLUP spec by ~15 years. Legacy MySQL code uses WITH ROLLUP. New code should use ROLLUP().

Question. Given a MySQL query using WITH ROLLUP, rewrite to ANSI ROLLUP.

Input.

-- MySQL legacy
SELECT region, country, SUM(revenue)
FROM sales
GROUP BY region, country WITH ROLLUP;

-- ANSI (works on MySQL 8.0.28+)
SELECT region, country, SUM(revenue)
FROM sales
GROUP BY ROLLUP (region, country);

Code. Both queries produce the same result.

Step-by-step explanation.

GROUP BY a, b WITH ROLLUP was MySQL's pre-ANSI spelling. Behaviourally identical to GROUP BY ROLLUP(a, b).
Both produce 3 grouping sets: (a, b), (a), ().
Prefer the ANSI spelling in new code — portable to Postgres, SQL Server, warehouses without change.
Pre-MySQL-8.0.28, WITH ROLLUP had restrictions with ORDER BY — you couldn't ORDER BY in the same statement. Workaround was a subquery.
Modern MySQL — either works. Consistency with the ANSI spelling is the tie-breaker.

Output.

region	country	SUM(revenue)
US	US	220
EU	UK	120
EU	DE	60
US	NULL	220
EU	NULL	180
NULL	NULL	400

Rule of thumb. In new MySQL code, use ANSI ROLLUP(...). In legacy code, WITH ROLLUP still works; refactor when touched.

`sql rollup` interview question on hierarchical vs arbitrary sets

A senior interviewer asks: "You need a report showing revenue by (region, country, city) + (region, country) subtotal + (region) subtotal + grand total, but NOT the (city) alone subtotal. Which do you use — ROLLUP or GROUPING SETS — and why?"

Solution Using ROLLUP for exact hierarchical fit

SELECT
  CASE WHEN GROUPING(region)  = 1 THEN 'All Regions'   ELSE region  END AS region_label,
  CASE WHEN GROUPING(country) = 1 THEN 'All Countries' ELSE country END AS country_label,
  CASE WHEN GROUPING(city)    = 1 THEN 'All Cities'    ELSE city    END AS city_label,
  SUM(revenue) AS revenue,
  GROUPING_ID(region, country, city) AS grouping_id
FROM sales
GROUP BY ROLLUP (region, country, city)
ORDER BY GROUPING_ID(region, country, city), region, country, city;

Step-by-step trace.

Grouping set	grouping_id	Purpose
(region, country, city)	0	Detail row
(region, country)	1	City subtotal
(region)	3	Country subtotal
()	7	Grand total

ROLLUP produces exactly these 4 sets. If we needed a (city) marginal (aggregate across all region+country for each city), we'd use GROUPING SETS instead. Since we don't, ROLLUP is a perfect fit — shorter, more expressive, conveys hierarchy.

Output:

Answer	Reasoning
ROLLUP fits perfectly	Wanted sets are hierarchical, ROLLUP produces exactly these 4 grouping sets
GROUPING SETS would work but is verbose	Same result, more typing
CUBE would over-produce	2^3 = 8 sets, includes (city), (city, country), etc. — not wanted

Why this works — concept by concept:

ROLLUP argument order = hierarchy — (region, country, city) means "start with detail, drop city, drop country, drop region." Exactly the wanted subtotals.
N+1 sets from N dims — 3 dims → 4 sets. Matches wanted output exactly.
GROUPING_ID for ordering — sort detail first, subtotals in hierarchy order, grand total last.
Formatted labels — CASE WHEN GROUPING(col) = 1 THEN 'All ...' ELSE col. Every P&L needs this.
Cost — single scan of sales. Modern warehouses execute all 4 grouping sets in one aggregate operator.

SQL
Topic — aggregation
SQL aggregation drills

Practice →

SQL Topic — SQL SQL practice library

Practice →

4. CUBE for full cross-tab aggregation

`sql cube` — the combinatorial primitive that produces all 2^N grouping sets in a single scan, ideal for cross-tab reports and pivot-style dashboards

The mental model in one line: CUBE(a, b, c) expands to every possible subset of the dimensions — 2^N grouping sets — producing the complete OLAP cube of subtotals in one query; use it when you need full cross-tab output (all row totals, all column totals, all combinations) and the dimension count is small enough that 2^N is manageable.

Slot 1 — the grammar.

GROUP BY CUBE(a, b, c) — expands to GROUPING SETS ((a,b,c), (a,b), (a,c), (b,c), (a), (b), (c), ()).
N dimensions produce 2^N grouping sets. Every subset.
Non-directional. Unlike ROLLUP, CUBE argument order doesn't affect which sets are produced.
Combinatorial output. 2^N grouping sets means output row count grows exponentially in N.
The empty set is always included. Grand total in every CUBE.
Composable with plain GROUP BY. GROUP BY CUBE(a, b), c combines CUBE with a fixed column.

Slot 2 — the 2^N growth math.

N=1 — CUBE = 2 sets. Same as ROLLUP.
N=2 — CUBE = 4 sets: (a,b), (a), (b), (). vs ROLLUP = 3 sets.
N=3 — CUBE = 8 sets. vs ROLLUP = 4 sets.
N=4 — CUBE = 16 sets. vs ROLLUP = 5 sets.
N=5 — CUBE = 32 sets. vs ROLLUP = 6 sets.
N=10 — CUBE = 1024 sets. Rarely appropriate.
Rule of thumb. Prefer CUBE only when N ≤ 4. Above that, use explicit GROUPING SETS with the combinations you actually need.

Slot 3 — cross-tab use cases.

Sales pivot table. Sales by (region, product). CUBE gives (region, product) detail + (region) row totals + (product) column totals + grand total. Exactly a spreadsheet PivotTable.
A/B test cross-tab. Conversions by (variant, segment). CUBE gives all subtotals.
Marketing spend matrix. Spend by (channel, campaign). Cross-tab.
Warehouse cost matrix. Cost by (project, dataset). Cross-tab.

Slot 4 — when NOT to use CUBE.

N > 5 dimensions. 32+ grouping sets — usually more subtotals than any dashboard renders.
When you only need hierarchical subtotals. Use ROLLUP.
When you need specific combinations, not all of them. Use GROUPING SETS.
When output cardinality exceeds visualisation limits. BI tools cap at ~50 K rows; CUBE on high-cardinality dimensions blows past.
Cost-per-run matters. Even single-scan CUBE has aggregate overhead; unnecessary combinations waste compute.

Slot 5 — the CUBE(a, b) equivalence.

CUBE(a, b) = GROUPING SETS ((a, b), (a), (b), ()).
Detail (a, b) + row subtotal (a) + column subtotal (b) + grand total.
Output row count = distinct(a,b) + distinct(a) + distinct(b) + 1.
Cost — one scan; aggregate with 4 output grains.

Slot 6 — CUBE with WHERE, HAVING, ORDER BY.

WHERE — pre-aggregation. Standard.
HAVING — post-aggregation. Filters per grouping set.
ORDER BY — post everything. Use GROUPING_ID for sorting.

Slot 7 — the plan shape.

Postgres. MixedAggregate with 2^N grains. Single scan.
SQL Server. Hash Aggregate typically; Stream Aggregate if input pre-sorted.
Snowflake. Single-stage aggregate with multi-grain output.
BigQuery. Single-stage.
Databricks. Photon-accelerated single-stage.
Cost. Slightly higher than ROLLUP because more grains to compute, but still single-scan.

Slot 8 — interview probes on CUBE.

"When would you use CUBE vs ROLLUP?" — CUBE for all subsets (cross-tab); ROLLUP for hierarchical.
"What's the cost of CUBE(a, b, c, d)?" — 2^4 = 16 grouping sets. Single scan but more aggregate output.
"Why is CUBE(a, b, c) equivalent to GROUPING SETS on all 8 subsets?" — because the CUBE spec defines it that way.
"When would you avoid CUBE?" — high N (blowup), or when only hierarchical or specific sets are needed.

Common beginner mistakes

Using CUBE with N > 5 dimensions — usually produces more subtotals than intended.
Confusing CUBE with ROLLUP output — they diverge for N > 1.
Ordering CUBE output without GROUPING_ID — subtotal rows scatter.
Not formatting subtotal labels — raw NULLs leak into reports.

Worked example — CUBE(region, product) for the sales cross-tab

Detailed explanation. The canonical "sales pivot table" — CUBE gives detail + row totals + column totals + grand total in one query.

Question. Given sales(region, product, revenue), write a CUBE query producing the full cross-tab report.

Input.

region	product	revenue
US	Widget	100
US	Gadget	50
EU	Widget	80
EU	Gadget	40

Code.

SELECT
  CASE WHEN GROUPING(region)  = 1 THEN 'All Regions'  ELSE region  END AS region_label,
  CASE WHEN GROUPING(product) = 1 THEN 'All Products' ELSE product END AS product_label,
  SUM(revenue) AS revenue,
  GROUPING_ID(region, product) AS grouping_id
FROM sales
GROUP BY CUBE (region, product)
ORDER BY GROUPING_ID(region, product), region, product;

Step-by-step explanation.

CUBE(region, product) = 4 grouping sets: (region, product), (region), (product), ().
Detail (region, product) — 4 rows.
Region row totals (region) — 2 rows.
Product column totals (product) — 2 rows.
Grand total () — 1 row.
Total = 9 rows. Exactly what a spreadsheet PivotTable would show.

Output.

region_label	product_label	revenue	grouping_id
US	Widget	100	0
US	Gadget	50	0
EU	Widget	80	0
EU	Gadget	40	0
US	All Products	150	1
EU	All Products	120	1
All Regions	Widget	180	2
All Regions	Gadget	90	2
All Regions	All Products	270	3

Rule of thumb. CUBE is the SQL PivotTable. Use it when you want detail + all row totals + all column totals + grand total in one shot.

Worked example — CUBE with 3 dimensions producing 8 grouping sets

Detailed explanation. With 3 dimensions, CUBE produces 2^3 = 8 grouping sets. Every combination of aggregated / preserved for each dimension.

Question. Given sales(region, product, quarter, revenue), write a CUBE query with 3 dimensions.

Input. Sales with region ∈ {US, EU}, product ∈ {Widget, Gadget}, quarter ∈ {Q1, Q2}.

Code.

SELECT
  region,
  product,
  quarter,
  SUM(revenue) AS revenue,
  GROUPING_ID(region, product, quarter) AS grouping_id
FROM sales
GROUP BY CUBE (region, product, quarter)
ORDER BY grouping_id, region, product, quarter;

Step-by-step explanation.

CUBE(3 dims) = 2^3 = 8 grouping sets.
Detail — 8 rows (2 regions × 2 products × 2 quarters).
(region, product) — 4 rows.
(region, quarter) — 4 rows.
(product, quarter) — 4 rows.
(region) — 2 rows.
(product) — 2 rows.
(quarter) — 2 rows.
() — 1 row.
Total = 8 + 4 + 4 + 4 + 2 + 2 + 2 + 1 = 27 rows. Non-trivial output; but 1 scan of sales.

Output. (Sample rows — full table has 27.)

region	product	quarter	revenue	grouping_id
US	Widget	Q1	100	0
US	Widget	Q2	120	0
US	Widget	NULL	220	1
US	NULL	Q1	150	2
NULL	Widget	Q1	180	4
US	NULL	NULL	340	3
NULL	Widget	NULL	400	5
NULL	NULL	Q1	330	6
NULL	NULL	NULL	720	7
...	...	...	...	...

Rule of thumb. CUBE with 3 dims is usually the practical max — 8 subtotals is manageable. Above that, use explicit GROUPING SETS.

Worked example — when to use CUBE vs GROUPING SETS

Detailed explanation. Decision-tree for choosing between the primitives.

Question. Given 4 dimensions (region, product, quarter, channel), which primitive would you use in each scenario?

Scenarios.

Scenario A — All 2^4 = 16 subtotals needed (full cross-tab).
Scenario B — Only hierarchical subtotals (region → product → quarter → channel).
Scenario C — Only detail + region-quarter + product-channel + grand total.
Scenario D — Only detail + region + quarter + grand total.

Code.

-- Scenario A — full cross-tab, 16 sets
GROUP BY CUBE (region, product, quarter, channel);

-- Scenario B — hierarchical, 5 sets
GROUP BY ROLLUP (region, product, quarter, channel);

-- Scenario C — arbitrary custom, use GROUPING SETS
GROUP BY GROUPING SETS (
  (region, product, quarter, channel),
  (region, quarter),
  (product, channel),
  ()
);

-- Scenario D — sparse custom, use GROUPING SETS
GROUP BY GROUPING SETS (
  (region, product, quarter, channel),
  (region),
  (quarter),
  ()
);

Step-by-step explanation.

Scenario A — all subsets: CUBE.
Scenario B — hierarchical: ROLLUP.
Scenario C — arbitrary combinations: GROUPING SETS.
Scenario D — sparse arbitrary combinations: GROUPING SETS.
General rule — if what you want matches a CUBE or ROLLUP fanout exactly, use them. Otherwise use GROUPING SETS.

Output. Decision tree in the table below.

Want	Use	Set count
All 2^N subsets	CUBE	2^N
N+1 hierarchical	ROLLUP	N+1
Arbitrary explicit	GROUPING SETS	Custom

Rule of thumb. CUBE and ROLLUP are sugar for common GROUPING SETS patterns. When your ask matches one, use it. Otherwise use GROUPING SETS.

`sql cube` interview question on cross-tab report

A senior interviewer asks: "Design a marketing spend cross-tab: spend by (channel, campaign, region) with row totals per channel, column totals per campaign, and per-region marginal. Use the smallest set of grouping sets that produces exactly this."

Solution Using GROUPING SETS (not CUBE) for a precise subset

-- CUBE would produce 2^3 = 8 sets, but we only want 5.
-- Use GROUPING SETS to name them exactly.
SELECT
  CASE WHEN GROUPING(channel)  = 1 THEN 'All Channels'  ELSE channel  END AS channel_label,
  CASE WHEN GROUPING(campaign) = 1 THEN 'All Campaigns' ELSE campaign END AS campaign_label,
  CASE WHEN GROUPING(region)   = 1 THEN 'All Regions'   ELSE region   END AS region_label,
  SUM(spend) AS spend,
  GROUPING_ID(channel, campaign, region) AS grouping_id
FROM marketing_spend
GROUP BY GROUPING SETS (
  (channel, campaign, region),   -- detail
  (channel),                     -- channel row totals
  (campaign),                    -- campaign column totals
  (region),                      -- per-region marginal
  ()                             -- grand total
)
ORDER BY grouping_id, channel, campaign, region;

Step-by-step trace.

Grouping set	grouping_id	Purpose
(channel, campaign, region)	0	Detail rows
(channel)	6	Channel row total
(campaign)	5	Campaign column total
(region)	3	Region marginal
()	7	Grand total

We omit CUBE's other 3 combinations — (channel, campaign), (channel, region), (campaign, region). If the dashboard doesn't render them, they'd be wasted rows.

Output:

Approach	Set count	Output rows	Cost
CUBE(channel, campaign, region)	8	detail + 7 subtotals	Higher
GROUPING SETS (custom 5)	5	detail + 4 subtotals	Lower
Savings	3 sets	~30% fewer rows	Aggregate step slightly faster

Why this works — concept by concept:

Explicit sets = intent — the query documents exactly what subtotals the dashboard needs. Future readers don't have to guess whether all 8 CUBE combinations are meaningful.
Slightly cheaper — fewer output rows to aggregate. Not a huge win in wall clock, but real in cost per query.
Format consistency — using GROUPING() with CASE labels gives readable output. Grand total row and per-set subtotals are self-documenting.
Ordering — GROUPING_ID first, then dimensions. Detail rows appear together; subtotals grouped; grand total last.
Cost — single scan of marketing_spend. Aggregate output has fewer grains than CUBE, so aggregate step is slightly cheaper.

SQL
Topic — aggregation
SQL aggregation drills

Practice →

SQL Topic — SQL SQL practice library

Practice →

5. GROUPING() function + dialect matrix + patterns

The `grouping function` and `grouping_id function` — the trick that distinguishes data-NULL from subtotal-NULL, and the 8-engine dialect matrix

The mental model in one line: GROUPING(col) returns 1 when col is aggregated at this output row's grain (i.e., the NULL is a subtotal-NULL) and 0 when col is preserved (i.e., the NULL, if any, is a data-NULL from the source column); GROUPING_ID(col1, col2, ...) returns the integer bitmask across multiple columns, letting you identify the grouping set by index. Master these two functions and you can format any multi-dim aggregation output cleanly.

Slot 1 — the GROUPING(col) function.

Signature — GROUPING(col) returns INT (0 or 1).
Returns 1 when col is aggregated at this grouping set's grain (i.e., the col value in output is a subtotal-NULL).
Returns 0 when col is preserved at this grouping set's grain (i.e., the col value in output is a data value, possibly data-NULL).
Use in SELECT — SELECT GROUPING(region) AS is_region_agg.
Use in HAVING — HAVING GROUPING(region) = 1 OR SUM(revenue) > 1000.
Use in ORDER BY — ORDER BY GROUPING(region), region.
Use with CASE — CASE WHEN GROUPING(region) = 1 THEN 'All Regions' ELSE region END. The canonical label pattern.

Slot 2 — the GROUPING_ID(...) function.

Signature — GROUPING_ID(col1, col2, ..., colN) returns INT.
Returns bitmask. Bit N-1 is 1 if col1 is aggregated; bit N-2 for col2; ...; bit 0 for colN. Some engines reverse the bit order — check your dialect.
Postgres, SQL Server, Snowflake, BigQuery, Databricks, DuckDB. GROUPING_ID(a, b) — bit 0 for b, bit 1 for a. So a aggregated = 2, b aggregated = 1, both aggregated = 3.
Convenience for ORDER BY / grouping-set identification. Instead of writing GROUPING(a) + GROUPING(b) * 2, use GROUPING_ID(a, b).
Convenience for CASE detection. Single integer to switch on, instead of multiple boolean checks.

Slot 3 — the label-formatting pattern.

Canonical. CASE WHEN GROUPING(col) = 1 THEN 'All Xs' ELSE col END AS col_label.
Numeric. CASE WHEN GROUPING(quantity) = 1 THEN NULL ELSE quantity END. Preserves quantity for detail; NULLs subtotal rows (some dashboards prefer this).
HTML. CASE WHEN GROUPING(col) = 1 THEN '<strong>Total</strong>' ELSE col END. If HTML output.
Zero-filled. COALESCE(col, 'N/A') — treats data-NULL as N/A. Combine with GROUPING() to distinguish.
Multi-dim. CASE WHEN GROUPING_ID(a, b) = 3 THEN 'Grand Total' WHEN GROUPING(a) = 1 THEN 'All As' WHEN GROUPING(b) = 1 THEN 'All Bs' ELSE a || ' / ' || b END.

Slot 4 — the 8-engine dialect matrix.

Engine	GROUPING SETS	ROLLUP	CUBE	GROUPING()	GROUPING_ID()
Postgres 9.5+	✅	✅	✅	✅	✅ (as bit-shift, since PG 9.5)
MySQL 8.0.28+	✅	✅	❌ (workaround via GROUPING SETS)	✅	❌ (compute manually)
SQL Server 2008+	✅	✅	✅	✅	✅
Oracle 8i+	✅	✅	✅	✅	✅
Snowflake	✅	✅	✅	✅	✅
BigQuery	✅	✅	✅	✅ (via `GROUPING`)	✅
Databricks (Spark 3+)	✅	✅	✅	✅	✅
DuckDB	✅	✅	✅	✅	✅

Slot 5 — engine-specific quirks.

Postgres. Fully ANSI-compliant since PG 9.5 (2016). Prior to 9.5, no native ROLLUP / CUBE — required workarounds.
MySQL 8.0.28+. GROUPING SETS + ROLLUP + GROUPING() supported. CUBE isn't native but can be emulated via GROUPING SETS on all subsets.
SQL Server. Full support since 2008. Also supports GROUP BY GROUPING SETS ((), (a)) — the tuple syntax.
Oracle. Full support since 8i (1999). Longest history among these engines.
Snowflake. Full support. GROUPING_ID follows the standard bitmask semantics.
BigQuery. Full support. Cost is by bytes scanned — GROUPING SETS is 1 scan cost, same as a plain GROUP BY.
Databricks. Full support via Spark. Photon accelerates CUBE / ROLLUP substantially.
DuckDB. Full support. In-process OLAP engine — GROUPING SETS is a first-class primitive.

Slot 6 — GROUPING vs GROUPING_ID choice.

Use GROUPING(col) — single-column check. GROUPING(region) = 1.
Use GROUPING_ID(col1, col2) — multi-column bitmask. Faster to write and read than multiple GROUPING() calls.
In HAVING — either works. GROUPING() is more readable.
In ORDER BY — GROUPING_ID is cleaner for multi-column ordering.
In CASE — GROUPING_ID for many combinations; GROUPING() for one.

Slot 7 — PIVOT vs GROUPING SETS decision tree.

PIVOT — turns row values into columns. Output is a matrix with fixed column headers.
GROUPING SETS — produces multi-grain rows. Output is a table with subtotal rows.
Use PIVOT — when the visualisation is a matrix (e.g., a bar chart with time on X-axis, region as bars). Columns are predefined.
Use GROUPING SETS — when the output is a table with subtotal rows (e.g., a P&L report). Rows include subtotals.
Warehouse support for PIVOT. SQL Server (native), Oracle (native), Snowflake (native), BigQuery (via SELECT ... FROM ... PIVOT (...)), Postgres (via crosstab extension), MySQL (no native), Databricks (native), DuckDB (native).

Slot 8 — combining GROUPING SETS with window functions.

Common pattern — compute multi-grain totals with GROUPING SETS, then use a window function to compute per-row percentages of grand total.
Skeleton.

WITH agg AS (
  SELECT dim1, dim2, SUM(...) AS metric, GROUPING_ID(dim1, dim2) AS gid
  FROM t
  GROUP BY GROUPING SETS (...)
)
SELECT
  ...,
  100.0 * metric / MAX(CASE WHEN gid = ... THEN metric END) OVER () AS pct
FROM agg;

The GROUPING SETS produces multi-grain aggregate; the window picks the grand-total row's value and broadcasts. Combine into one query.

Slot 9 — SCD Type 2 dimension queries with GROUPING SETS.

Time-aware GROUPING SETS. For an SCD Type 2 dimension, filter by valid_from <= as_of AND valid_to > as_of before GROUPING SETS aggregation. Point-in-time queries.
Snapshot pattern. GROUPING SETS on the current version only — WHERE is_current.

Common beginner mistakes

Confusing GROUPING with GROUPING_ID — the former is per-column, the latter is a bitmask.
Using COALESCE to hide subtotal NULLs — you lose the ability to distinguish from data-NULL.
Forgetting GROUPING_ID bit order — check your engine's convention (leftmost or rightmost).
Using MySQL 8.0.28 with GROUPING SETS on older code — the syntax was added in 8.0.28 (2022).
Assuming PIVOT and GROUPING SETS are interchangeable — they produce different shapes (matrix vs subtotal-rows).

Worked example — GROUPING vs GROUPING_ID on a 3-dim CUBE

Detailed explanation. With 3 dimensions and CUBE, you get 8 grouping sets. Use GROUPING_ID to identify each one by integer.

Question. Given a 3-dim CUBE, show how GROUPING_ID identifies each grouping set.

Input. 3-dim CUBE(a, b, c).

Code.

SELECT
  a, b, c,
  SUM(x) AS x,
  GROUPING(a) AS ga,
  GROUPING(b) AS gb,
  GROUPING(c) AS gc,
  GROUPING_ID(a, b, c) AS gid
FROM t
GROUP BY CUBE (a, b, c);

Step-by-step explanation.

3-dim CUBE = 8 grouping sets.
Each grouping set has a unique GROUPING_ID:
- (a, b, c) — nothing aggregated → gid = 0 (binary 000).
- (a, b) — c aggregated → gid = 1 (binary 001).
- (a, c) — b aggregated → gid = 2 (binary 010).
- (a) — b and c aggregated → gid = 3 (binary 011).
- (b, c) — a aggregated → gid = 4 (binary 100).
- (b) — a and c aggregated → gid = 5 (binary 101).
- (c) — a and b aggregated → gid = 6 (binary 110).
- () — all aggregated → gid = 7 (binary 111).
Sort by gid to see detail first, then partial subtotals, then grand total.
Note bit order — GROUPING_ID(a, b, c) — a is bit 2, b is bit 1, c is bit 0. Most engines follow this convention.

Output.

Grouping set	gid	ga	gb	gc
(a, b, c)	0	0	0	0
(a, b)	1	0	0	1
(a, c)	2	0	1	0
(a)	3	0	1	1
(b, c)	4	1	0	0
(b)	5	1	0	1
(c)	6	1	1	0
()	7	1	1	1

Rule of thumb. GROUPING_ID is the fast identifier for grouping sets. Learn the bit order for your engine.

Worked example — subtotal labels with CASE + GROUPING

Detailed explanation. The canonical pattern for formatting subtotal rows with human-readable labels.

Question. Format the output of a GROUPING SETS query so subtotal rows show "All Regions" / "All Products" / "Grand Total" instead of NULL.

Input. Result of GROUPING SETS ((region, product), (region), (product), ()).

Code.

SELECT
  CASE
    WHEN GROUPING(region) = 1 AND GROUPING(product) = 1 THEN 'Grand Total'
    WHEN GROUPING(region) = 1 THEN 'All Regions'
    ELSE region
  END AS region_label,
  CASE
    WHEN GROUPING(product) = 1 THEN 'All Products'
    ELSE product
  END AS product_label,
  SUM(revenue) AS revenue,
  GROUPING_ID(region, product) AS grouping_id
FROM sales
GROUP BY GROUPING SETS ((region, product), (region), (product), ())
ORDER BY grouping_id, region, product;

Step-by-step explanation.

Grand total row: both GROUPING(region) and GROUPING(product) are 1. Label as "Grand Total" in region column, "All Products" in product column.
Region subtotal: GROUPING(region) = 0, GROUPING(product) = 1. region_label = region value; product_label = "All Products".
Product subtotal: GROUPING(region) = 1, GROUPING(product) = 0. region_label = "All Regions"; product_label = product value.
Detail: both are 0. Both labels = raw values.
Users see human-readable labels instead of NULLs.

Output.

region_label	product_label	revenue	grouping_id
US	Widget	100	0
US	Gadget	50	0
EU	Widget	80	0
EU	Gadget	40	0
US	All Products	150	1
EU	All Products	120	1
All Regions	Widget	180	2
All Regions	Gadget	90	2
Grand Total	All Products	270	3

Rule of thumb. Every BI-facing GROUPING SETS query needs label formatting. Never let subtotal-NULLs leak into a user-facing report.

Worked example — GROUPING SETS combined with a window function for percentages

Detailed explanation. Report % of grand total per row. Window function references the grand-total row.

Question. Extend the previous query to include pct_of_grand_total.

Input. Same.

Code.

WITH agg AS (
  SELECT
    region,
    product,
    SUM(revenue) AS revenue,
    GROUPING_ID(region, product) AS grouping_id
  FROM sales
  GROUP BY GROUPING SETS ((region, product), (region), (product), ())
)
SELECT
  CASE WHEN grouping_id >= 2 THEN 'All Regions' ELSE region END AS region_label,
  CASE WHEN grouping_id IN (1, 3) THEN 'All Products' ELSE product END AS product_label,
  revenue,
  ROUND(
    100.0 * revenue / MAX(CASE WHEN grouping_id = 3 THEN revenue END) OVER (),
    2
  ) AS pct_of_grand_total
FROM agg
ORDER BY grouping_id, region, product;

Step-by-step explanation.

CTE agg materialises the multi-grain aggregation.
MAX(CASE WHEN grouping_id = 3 THEN revenue END) OVER () — window over the whole result. Picks the grand total row's revenue (270).
Each row's revenue / 270 * 100 = its % of grand total.
Detail rows show their fraction; subtotal rows also compute their fraction; grand total is 100%.
The window sees all rows because there's no PARTITION BY.

Output.

region_label	product_label	revenue	pct_of_grand_total
US	Widget	100	37.04
US	Gadget	50	18.52
EU	Widget	80	29.63
EU	Gadget	40	14.81
US	All Products	150	55.56
EU	All Products	120	44.44
All Regions	Widget	180	66.67
All Regions	Gadget	90	33.33
All Regions	All Products	270	100.00

Rule of thumb. GROUPING SETS + window is the finance-dashboard combination. Multi-grain totals plus per-row references. Every senior SQL loop asks about this pattern.

`grouping function` interview question on multi-dim P&L formatting

A senior interviewer asks: "Write a Snowflake P&L query on sales(region, country, quarter, revenue, cost) that uses ROLLUP for hierarchical subtotals and includes: (a) formatted labels via GROUPING(), (b) profit calculation, (c) % of grand-total revenue via window, (d) sorted by hierarchy."

Solution Using ROLLUP + GROUPING() + window function

WITH agg AS (
  SELECT
    region,
    country,
    quarter,
    SUM(revenue) AS revenue,
    SUM(cost) AS cost,
    SUM(revenue - cost) AS profit,
    GROUPING_ID(region, country, quarter) AS grouping_id
  FROM sales
  GROUP BY ROLLUP (region, country, quarter)
),
grand_total AS (
  SELECT MAX(CASE WHEN grouping_id = 7 THEN revenue END) AS grand_revenue
  FROM agg
)
SELECT
  CASE WHEN GROUPING(region)  = 1 THEN 'All Regions'   ELSE region  END AS region_label,
  CASE WHEN GROUPING(country) = 1 THEN 'All Countries' ELSE country END AS country_label,
  CASE WHEN GROUPING(quarter) = 1 THEN 'All Quarters'  ELSE quarter END AS quarter_label,
  revenue,
  cost,
  profit,
  ROUND(100.0 * revenue / gt.grand_revenue, 2) AS pct_of_total_revenue,
  grouping_id
FROM agg
CROSS JOIN grand_total gt
GROUP BY ALL   -- Snowflake shortcut; else list all
ORDER BY grouping_id, region, country, quarter;

Step-by-step trace.

Wait — the CTE agg already has GROUPING_ID; the top-level SELECT doesn't need another GROUP BY. Corrected:

WITH agg AS (
  SELECT
    region, country, quarter,
    SUM(revenue) AS revenue,
    SUM(cost) AS cost,
    SUM(revenue - cost) AS profit,
    GROUPING_ID(region, country, quarter) AS grouping_id,
    GROUPING(region)  AS g_region,
    GROUPING(country) AS g_country,
    GROUPING(quarter) AS g_quarter
  FROM sales
  GROUP BY ROLLUP (region, country, quarter)
)
SELECT
  CASE WHEN g_region  = 1 THEN 'All Regions'   ELSE region  END AS region_label,
  CASE WHEN g_country = 1 THEN 'All Countries' ELSE country END AS country_label,
  CASE WHEN g_quarter = 1 THEN 'All Quarters'  ELSE quarter END AS quarter_label,
  revenue,
  cost,
  profit,
  ROUND(
    100.0 * revenue / MAX(CASE WHEN grouping_id = 7 THEN revenue END) OVER (),
    2
  ) AS pct_of_total_revenue,
  grouping_id
FROM agg
ORDER BY grouping_id, region, country, quarter;

Grouping set	grouping_id	Purpose
(region, country, quarter)	0	Detail
(region, country)	1	Quarter subtotal
(region)	3	Country subtotal
()	7	Grand total

Output: Multi-grain P&L rows with formatted labels and percentages.

Why this works — concept by concept:

ROLLUP for hierarchy — natural fit for region → country → quarter drill-down. 4 grouping sets = N+1.
Materialised agg in CTE — computes all grains once. Downstream SELECT references without re-aggregating.
GROUPING flags in the CTE — pre-computed and carried to the output. Cleaner than calling GROUPING() again in the outer SELECT.
Window for grand-total broadcast — MAX(CASE WHEN grouping_id = 7 THEN revenue END) OVER () picks the grand-total row's revenue and broadcasts to all rows. Each row's percentage is computed against it.
ORDER BY grouping_id first — detail rows first, then subtotals in hierarchy order, grand total last. Natural report layout.
Cost — Single scan of sales. Aggregate in one pass. Window is O(N output rows). Post-processing (labels, ORDER) is trivial.

SQL
Topic — aggregation
SQL aggregation drills

Practice →

SQL Topic — window functions Window function drills

Practice →

Cheat sheet — GROUPING SETS / ROLLUP / CUBE recipe list

Three primitives. GROUPING SETS (arbitrary), ROLLUP (hierarchical), CUBE (all subsets).
GROUPING SETS skeleton. GROUP BY GROUPING SETS ((a,b), (a), (b), ()). Manual, explicit.
ROLLUP skeleton. GROUP BY ROLLUP(a, b, c) = 4 sets: (a,b,c), (a,b), (a), (). Hierarchical, N+1.
CUBE skeleton. GROUP BY CUBE(a, b, c) = 8 sets. Combinatorial, 2^N.
Empty set () = grand total. Include in GROUPING SETS explicitly; automatic in ROLLUP and CUBE.
GROUPING(col) returns 0 or 1. 1 = column aggregated at this row's grain (subtotal-NULL). 0 = column preserved.
GROUPING_ID(a, b, c) returns bitmask. Bit N-1 = a, bit 0 = c. Sort by GROUPING_ID for detail-first, grand-total-last ordering.
Label pattern. CASE WHEN GROUPING(col) = 1 THEN 'All ...' ELSE col END. Never leak subtotal-NULLs.
N+1 vs 2^N. ROLLUP = N+1 sets; CUBE = 2^N sets. Choose based on wanted output shape.
Directional ROLLUP. Argument order matters. ROLLUP(region, country) ≠ ROLLUP(country, region).
Non-directional CUBE. Argument order doesn't affect output.
MySQL legacy. GROUP BY a, b WITH ROLLUP = old spelling. ANSI ROLLUP(a, b) since MySQL 8.0.28.
Postgres. Full support since PG 9.5 (2016).
SQL Server. Full support since 2008.
Oracle. Full support since 8i (1999).
Snowflake / BigQuery / Databricks / DuckDB. Full support.
Single-scan cost. All modern engines execute in one scan of the fact table. Cheaper than UNION ALL of N GROUP BYs.
HAVING with GROUPING(). HAVING GROUPING(col) = 1 OR SUM(x) > threshold — scope filters to subtotal / detail.
ORDER BY GROUPING_ID. Sort detail first, then subtotals in hierarchy order, then grand total.
Combine with window functions. GROUPING SETS in CTE, window in outer SELECT — the finance-dashboard pattern.
Combine with JOINs. JOIN fact + dim, then GROUPING SETS on the result.
CUBE growth. N=2 → 4 sets; N=3 → 8; N=5 → 32; N=10 → 1024. Prefer explicit GROUPING SETS for N > 5.
Dashboard use. BI tools like Tableau / Power BI / Looker either issue GROUPING SETS under the hood or generate separate queries for subtotals.
PIVOT alternative. PIVOT rotates rows to columns; GROUPING SETS produces multi-grain rows. Different output shapes; both are legitimate cross-tab tools.
dbt-style rollup. unique_key='id' + incremental_strategy combined with GROUPING SETS in the model SQL.
Cost gates on BigQuery. --dry_run before running large CUBE / ROLLUP; check bytes billed.
NULL semantics. Data-NULL and subtotal-NULL both show as NULL; only GROUPING() can distinguish.
Grand total row for KPIs. Every BI dashboard's "Grand Total" line at the bottom is (either explicit or implicit) GROUPING SETS ((detail_cols), ()).
HAVING is per-set. Filters apply per grouping set output. HAVING SUM(revenue) > 100 filters both detail and subtotal rows.
Ordering trade-off. Adding ORDER BY on aggregated results — cost is O(output rows), usually cheap.

Frequently asked questions

What's the difference between GROUPING SETS, ROLLUP, and CUBE?

All three primitives express multi-grain aggregation in a single query, but with different set-generation semantics: GROUPING SETS is the general-purpose form — you list every wanted grouping set explicitly, e.g., GROUPING SETS ((a,b), (a), (b), ()) for 4 arbitrary sets. ROLLUP is hierarchical — ROLLUP(a, b, c) expands to 4 grouping sets in a directional drill-down: (a,b,c), (a,b), (a), (). Drop the rightmost column at each step until empty. Use ROLLUP when your dimensions form a natural hierarchy (region → country → city, year → quarter → month). CUBE is combinatorial — CUBE(a, b, c) expands to all 2^N = 8 subsets, producing detail + every combination of subtotals + grand total. Use CUBE for cross-tab reports where you want row totals, column totals, and everything in between. All three run in a single scan of the fact table, so cost-wise they're equivalent to a single GROUP BY (roughly N× cheaper than N UNION ALL'd queries). Rule of thumb — GROUPING SETS for explicit control, ROLLUP for hierarchies, CUBE for full cross-tabs with small N.

When should I use ROLLUP vs GROUPING SETS?

Use ROLLUP when your dimensions form a natural directional hierarchy and you want exactly N+1 grouping sets in that order. Examples — geographic (region → country → city), temporal (year → quarter → month → day), organizational (department → team → individual). The shorter spelling (ROLLUP(a, b, c) vs GROUPING SETS ((a, b, c), (a, b), (a), ())) is more readable and conveys intent. Use GROUPING SETS when — (a) you need arbitrary, non-hierarchical combinations, e.g., detail + (region, product) + (channel) + grand total; (b) you want to omit a level (e.g., skip the region-only subtotal); (c) you want to combine multiple hierarchies in one query; or (d) you need dimensions unrelated to a hierarchy. Every ROLLUP can be expressed as GROUPING SETS; not every GROUPING SETS can be expressed as ROLLUP. When in doubt, use GROUPING SETS — it's always explicit. When you see a directional hierarchy you don't want to expand yourself, use ROLLUP.

How does the GROUPING() function work?

GROUPING(col) returns an integer — 1 when col is aggregated (i.e., the column value in this row is a subtotal-NULL) and 0 when col is preserved (i.e., the column value in this row is a data value, possibly a data-NULL). It's the trick that lets you distinguish "NULL because the query is aggregating" from "NULL because the source data was missing." Use in the SELECT to format subtotal labels — CASE WHEN GROUPING(region) = 1 THEN 'All Regions' ELSE region END AS region_label. Use in HAVING to filter — HAVING GROUPING(region) = 1 OR SUM(revenue) > 100 (keep subtotal rows unconditionally, but filter detail rows). Use in ORDER BY to sort — ORDER BY GROUPING(region), region puts detail rows first, subtotal rows last per region. For multi-column identification, use GROUPING_ID(a, b, c) — returns a bitmask (integer) with bit N-1 for the first column, bit 0 for the last. This lets you identify each grouping set by a single number, useful for CASE statements and ORDER BY. Every engine that supports GROUPING SETS / ROLLUP / CUBE also supports GROUPING() with the same semantics.

What's the cost of CUBE on a large table?

CUBE runs in a single scan of the fact table, same as any GROUP BY — the extra cost is only in the aggregate operator producing 2^N output grains instead of 1. On a 10 B-row fact table with 3 CUBE dimensions (8 grouping sets), the scan is still ~24 GB (compressed), but the aggregate output has ~8× the row count of a plain GROUP BY. Wall clock is typically 1.5–2× a plain GROUP BY, not 8× — the scan is the dominant cost. On BigQuery on-demand, bytes_processed is the same as a single GROUP BY, so cost is unchanged. On Snowflake and Databricks, warehouse-tier × wall clock — 1.5–2× a plain GROUP BY. The real cost problem is output row count. With 5 CUBE dimensions, you get 32 grouping sets — if each has ~10K distinct values, output is 320K rows. Ordering + downstream network transport can dominate. For dimensions > 5, always prefer explicit GROUPING SETS with only the combinations you actually need. Also verify with --dry_run (BigQuery) or the query profile (Snowflake) before shipping CUBE to production.

How do I combine GROUPING SETS with window functions?

The canonical pattern is a CTE with the GROUPING SETS aggregation followed by an outer SELECT with a window function referencing the grand-total row:

WITH agg AS (
  SELECT
    region, product,
    SUM(revenue) AS revenue,
    GROUPING_ID(region, product) AS grouping_id
  FROM sales
  GROUP BY GROUPING SETS ((region, product), (region), (product), ())
)
SELECT
  region, product, revenue,
  ROUND(
    100.0 * revenue / MAX(CASE WHEN grouping_id = 3 THEN revenue END) OVER (),
    2
  ) AS pct_of_grand_total
FROM agg;

The MAX(CASE WHEN grouping_id = 3 THEN revenue END) OVER () picks the grand-total row's revenue (grouping_id = 3 = both dimensions aggregated) and broadcasts it to every row via the empty OVER window. Each row can then compute its percentage against the grand total. Variations — use MAX(CASE WHEN grouping_id = 1 THEN revenue END) OVER (PARTITION BY region) for "% of region subtotal" per detail row. Use RANK() OVER (PARTITION BY grouping_id ORDER BY revenue DESC) to rank within each grouping set. Combine with ROW_NUMBER() for pagination within subtotals. The pattern generalises — GROUPING SETS in CTE gives you multi-grain aggregation; the window function gives you cross-row references and rankings. Every finance dashboard has one of these.

How do I format subtotal rows in the output?

Use CASE WHEN GROUPING(col) = 1 THEN 'Human Label' ELSE col END AS col_label for every column that appears in some grouping sets but not others. For a multi-column ROLLUP, combine multiple CASE expressions — one per column. For a grand-total row detection (all columns aggregated), check GROUPING_ID(a, b, c) = 7 (for 3 dims) or write WHEN GROUPING(a) = 1 AND GROUPING(b) = 1 AND GROUPING(c) = 1 THEN 'Grand Total'. A common pattern is a three-tier label: "Grand Total" for the empty set, "All Xs" for individual dimension aggregations, and the raw value for detail rows. In some dashboards, subtotal rows also want bold formatting — output <strong> tags or a marker column that downstream renderers use. Always sort by GROUPING_ID(...) first so detail rows appear before subtotals before grand total. Never let raw NULLs from subtotal aggregation leak into a user-facing report — every senior review comment will call this out.

Practice on PipeCode

Drill the SQL aggregation practice library → — GROUP BY, GROUPING SETS, ROLLUP, and CUBE patterns.
Sharpen SQL optimization drills → for reading multi-aggregation plans and rewriting UNION ALL to single-scan GROUPING SETS.
Layer window function drills → — the combo pattern of GROUPING SETS + window function shows up in every finance dashboard.
Warm up with SQL join drills → — GROUPING SETS on joined fact + dim tables.
Sharpen the general SQL surface with the SQL practice library → — 450+ DE-focused questions covering multi-dimensional aggregation.
For the broader SQL interview surface, take the SQL for Data Engineering course →.

Pipecode.ai is Leetcode for Data Engineering — every `sql grouping sets rollup cube` recipe above ships with hands-on practice rooms where you type `GROUP BY GROUPING SETS ((a,b),(a),(b),())` on a live Postgres, migrate the same query to `ROLLUP(region, country, city)` for hierarchical P&L, translate it to `CUBE(region, product)` for a full sales cross-tab, format subtotal rows with `CASE WHEN GROUPING(region) = 1 THEN 'All Regions' ELSE region END`, identify grouping sets with `GROUPING_ID(a, b, c)`, and combine multi-grain aggregation with window functions to compute per-row percentages of grand total — the exact multi-dimensional aggregation fluency that senior DE interviews probe. PipeCode pairs every GROUPING SETS / ROLLUP / CUBE concept with 450+ DE-focused problems and a real-time scoring engine, so you never have to wonder whether your subtotal-and-grand-total answer holds up under a senior interviewer's depth probes.

Practice aggregation now →
Window function drills →

1. Why multi-dimensional aggregation matters in 2026

The multi-dimensional aggregation mental model — why the naive UNION ALL of many GROUP BYs is quadratic, and what a single-scan aggregation buys you

Worked example — the naive UNION ALL vs GROUPING SETS

Worked example — reading the GROUPING() flag on subtotal rows

Worked example — combining GROUPING SETS with a window function for percentages

sql grouping sets interview question on refactoring UNION ALL to single-scan

Solution Using GROUPING SETS to collapse N scans into one

2. GROUPING SETS anatomy

sql grouping sets — the general-purpose multi-grain aggregation primitive that expresses N GROUP BYs as one

Worked example — the four-set GROUPING SETS with detail, two marginals, and grand total

Worked example — GROUPING SETS with a HAVING filter

Worked example — GROUPING SETS with a join

sql grouping sets interview question on building a P&L rollup report

Solution Using GROUPING SETS with formatted labels + window for % of total

3. ROLLUP for hierarchical subtotals

sql rollup — the directional, hierarchical subtotal primitive that produces N+1 grouping sets from N dimensions

Worked example — the ROLLUP(region, country, city) P&L rollup

Worked example — ROLLUP for a monthly-quarterly-yearly time series

Worked example — MySQL WITH ROLLUP legacy vs ANSI ROLLUP

sql rollup interview question on hierarchical vs arbitrary sets

Solution Using ROLLUP for exact hierarchical fit

4. CUBE for full cross-tab aggregation

sql cube — the combinatorial primitive that produces all 2^N grouping sets in a single scan, ideal for cross-tab reports and pivot-style dashboards

Worked example — CUBE(region, product) for the sales cross-tab

Worked example — CUBE with 3 dimensions producing 8 grouping sets

Worked example — when to use CUBE vs GROUPING SETS

sql cube interview question on cross-tab report

Solution Using GROUPING SETS (not CUBE) for a precise subset

5. GROUPING() function + dialect matrix + patterns

The grouping function and grouping_id function — the trick that distinguishes data-NULL from subtotal-NULL, and the 8-engine dialect matrix

Worked example — GROUPING vs GROUPING_ID on a 3-dim CUBE

Worked example — subtotal labels with CASE + GROUPING

Worked example — GROUPING SETS combined with a window function for percentages

grouping function interview question on multi-dim P&L formatting

Solution Using ROLLUP + GROUPING() + window function

Cheat sheet — GROUPING SETS / ROLLUP / CUBE recipe list

Frequently asked questions

What's the difference between GROUPING SETS, ROLLUP, and CUBE?

When should I use ROLLUP vs GROUPING SETS?

How does the GROUPING() function work?

What's the cost of CUBE on a large table?

How do I combine GROUPING SETS with window functions?

How do I format subtotal rows in the output?

Practice on PipeCode

The `multi-dimensional aggregation` mental model — why the naive UNION ALL of many GROUP BYs is quadratic, and what a single-scan aggregation buys you

`sql grouping sets` interview question on refactoring UNION ALL to single-scan

`sql grouping sets` — the general-purpose multi-grain aggregation primitive that expresses N GROUP BYs as one

`sql grouping sets` interview question on building a P&L rollup report

`sql rollup` — the directional, hierarchical subtotal primitive that produces N+1 grouping sets from N dimensions

Worked example — MySQL `WITH ROLLUP` legacy vs ANSI ROLLUP

`sql rollup` interview question on hierarchical vs arbitrary sets

`sql cube` — the combinatorial primitive that produces all 2^N grouping sets in a single scan, ideal for cross-tab reports and pivot-style dashboards

`sql cube` interview question on cross-tab report

The `grouping function` and `grouping_id function` — the trick that distinguishes data-NULL from subtotal-NULL, and the 8-engine dialect matrix

`grouping function` interview question on multi-dim P&L formatting