How do GROUP BY ROLLUP(a, b, c) and GROUP BY CUBE(a, b, c) differ in the grouping sets they generate?

middleshould knowfreq 44%

answer

One walks prefixes, one takes subsets
Counts are n+1 versus 2 to the n
Hierarchy versus independent dimensions
Column order matters for only one of them
Both include the full set and the grand total

basics

~20 s

ROLLUP generates the n+1 prefixes of the column list, so four grouping sets for three columns. CUBE generates every subset — 2^n, or eight for three columns — including combinations that skip a leading column.

solid answer

~40 s

ROLLUP(a, b, c) walks the prefixes: (a,b,c), (a,b), (a), () — n+1 sets, and column order is meaningful because columns drop off the right. CUBE(a, b, c) produces the full power set: all eight subsets, including (a,c), (b) and (b,c), so column order does not change the set of levels at all. Use ROLLUP when the columns form a hierarchy — country, state, city or year, month, day — because the prefixes are exactly the meaningful roll-up levels and (city) alone would be nonsense. Use CUBE for independent dimensions when you want a full cross-tabulation, for example region against payment method. CUBE's output grows exponentially in the number of columns, so beyond three or four dimensions it is usually better to spell out only the levels you need with GROUPING SETS.

`ROLLUP(a, b, c)` walks the prefixes: `(a,b,c)`, `(a,b)`, `(a)`, `()` — n+1 sets, and column order is meaningful because columns drop off the right. `CUBE(a, b, c)` produces the full power set: all eight subsets, including `(a,c)`, `(b)` and `(b,c)`, so column order does not change the set of levels at all. Use ROLLUP when the columns form a hierarchy — `country, state, city` or `year, month, day` — because the prefixes are exactly the meaningful roll-up levels and `(city)` alone would be nonsense. Use CUBE for independent dimensions when you want a full cross-tabulation, for example region against payment method. CUBE's output grows exponentially in the number of columns, so beyond three or four dimensions it is usually better to spell out only the levels you need with `GROUPING SETS`.

code

sql · 8 lines

sql

-- ROLLUP: prefixes only (4 grouping sets)
GROUP BY ROLLUP(country, state, city)
-- = GROUPING SETS ((country,state,city), (country,state), (country), ())

-- CUBE: every subset (8 grouping sets)
GROUP BY CUBE(country, state, city)
-- = GROUPING SETS ((country,state,city), (country,state), (country,city),
--                  (state,city), (country), (state), (city), ())

go deeper

for a junior

Recall the counts and the shape: ROLLUP gives n+1 prefix levels, CUBE gives 2^n subsets, and both include the detail level and the grand total.

for a middle

Explain the expansion of each into GROUPING SETS, why column order matters for ROLLUP but not CUBE, and pick the right one for a given set of columns with a reason.

for a senior

Show awareness of result-size blow-up as dimensions grow, prefer an explicit GROUPING SETS list when only some levels are wanted, and name the portability limits before promising CUBE in a shipped report.

for a principal

Decide where multi-level aggregation belongs at all: a grouping-set query per report, a pre-computed summary table, or the BI tool's own rollup — and what that choice costs in freshness, storage and engine portability.

Two shorthands over the same machinery

ROLLUP and CUBE are both abbreviations for a GROUPING SETS list. They differ only in which list they expand to.

ROLLUP takes prefixes, dropping columns from the right:

sql

GROUP BY ROLLUP(a, b, c)
-- = GROUPING SETS ((a,b,c), (a,b), (a), ())

That is n+1 grouping sets for n columns: 4 for three columns, 5 for four.

CUBE takes every subset — the power set:

sql

GROUP BY CUBE(a, b)
-- = GROUPING SETS ((a,b), (a), (b), ())

That is 2^n grouping sets: 4 for two columns, 8 for three, 16 for four. Both always include the full set and the empty set (), so the detail rows and the grand total are common to both; everything in between differs.

Column order matters for one and not the other

Because ROLLUP walks prefixes, ROLLUP(region, product) and ROLLUP(product, region) give different middle levels — subtotals per region in one case, per product in the other. CUBE contains every subset regardless of order, so CUBE(region, product) and CUBE(product, region) produce the same set of grouping levels (the column order in the output row still follows the select list).

Which to reach for

The deciding question is whether the columns form a hierarchy or are independent dimensions.

Hierarchy — country, state, city, year, quarter, month, category, subcategory: only prefixes make sense. Grouping by city without country would merge same-named cities in different countries, and grouping by month without year would mix Januaries across years. ROLLUP is the right shorthand, and it produces exactly the levels a drill-down report needs.
Independent dimensions — region and payment_method, channel and device: every subset is meaningful, and a cross-tab wants the row totals, the column totals and the grand total. That is CUBE.

When you want some but not all of the subsets, skip both shorthands and write the list explicitly with GROUPING SETS.

Result size

Row counts follow from the sets. For CUBE(a, b) over a table where a has 2 distinct values, b has 2, and all four combinations occur: 4 detail rows + 2 (per a) + 2 (per b) + 1 = 9 rows. The exponential term is in the number of columns, and each additional grouping set multiplies out against the cardinality of its columns, so a CUBE over five columns is 32 grouping sets and can dwarf the detail data. That is the usual reason to enumerate levels by hand.

Reading the output

Both produce super-aggregate rows whose excluded columns come back NULL, and both therefore need GROUPING() if the base data can contain NULLs in the grouping columns. With CUBE the level indicator matters more, because a row with region = NULL, method = 'card' is a per-method total while region = 'EU', method = NULL is a per-region total — the shape of a row is no longer as predictable as it is under a single ROLLUP hierarchy.

Nesting and mixing

The shorthands compose. A GROUP BY list may contain plain columns alongside them, and multiple grouping constructs multiply out:

sql

GROUP BY tenant_id, ROLLUP(region, city)
GROUP BY ROLLUP(year, month), CUBE(channel, device)

The first keeps tenant_id in every set; the second produces 3 × 4 = 12 grouping sets. Engines also allow parenthesised composite elements — ROLLUP((country, state), city) treats the pair as a single level so you never get a state without its country — though support for that spelling varies, so verify it against your engine's documentation.

Portability

ROLLUP and CUBE in the GROUP BY (...) form are the standard spellings and are accepted by PostgreSQL, Oracle, SQL Server and DB2. MySQL 8 offers only the trailing WITH ROLLUP syntax and has no CUBE at all; SQLite has neither. Where CUBE is unavailable, the fallback is a UNION ALL of the individual grouping queries — correct, but reading the input once per level.

## Two shorthands over the same machinery `ROLLUP` and `CUBE` are both abbreviations for a `GROUPING SETS` list. They differ only in which list they expand to. **ROLLUP** takes prefixes, dropping columns from the right: ```sql GROUP BY ROLLUP(a, b, c) -- = GROUPING SETS ((a,b,c), (a,b), (a), ()) ``` That is n+1 grouping sets for n columns: 4 for three columns, 5 for four. **CUBE** takes every subset — the power set: ```sql GROUP BY CUBE(a, b) -- = GROUPING SETS ((a,b), (a), (b), ()) ``` That is 2^n grouping sets: 4 for two columns, 8 for three, 16 for four. Both always include the full set and the empty set `()`, so the detail rows and the grand total are common to both; everything in between differs. ## Column order matters for one and not the other Because ROLLUP walks prefixes, `ROLLUP(region, product)` and `ROLLUP(product, region)` give different middle levels — subtotals per region in one case, per product in the other. CUBE contains every subset regardless of order, so `CUBE(region, product)` and `CUBE(product, region)` produce the same set of grouping levels (the column order in the output row still follows the select list). ## Which to reach for The deciding question is whether the columns form a **hierarchy** or are **independent dimensions**. - Hierarchy — `country, state, city`, `year, quarter, month`, `category, subcategory`: only prefixes make sense. Grouping by `city` without `country` would merge same-named cities in different countries, and grouping by `month` without `year` would mix Januaries across years. ROLLUP is the right shorthand, and it produces exactly the levels a drill-down report needs. - Independent dimensions — `region` and `payment_method`, `channel` and `device`: every subset is meaningful, and a cross-tab wants the row totals, the column totals and the grand total. That is CUBE. When you want some but not all of the subsets, skip both shorthands and write the list explicitly with `GROUPING SETS`. ## Result size Row counts follow from the sets. For `CUBE(a, b)` over a table where a has 2 distinct values, b has 2, and all four combinations occur: 4 detail rows + 2 (per a) + 2 (per b) + 1 = 9 rows. The exponential term is in the number of *columns*, and each additional grouping set multiplies out against the cardinality of its columns, so a CUBE over five columns is 32 grouping sets and can dwarf the detail data. That is the usual reason to enumerate levels by hand. ## Reading the output Both produce super-aggregate rows whose excluded columns come back NULL, and both therefore need `GROUPING()` if the base data can contain NULLs in the grouping columns. With CUBE the level indicator matters more, because a row with `region = NULL, method = 'card'` is a per-method total while `region = 'EU', method = NULL` is a per-region total — the shape of a row is no longer as predictable as it is under a single ROLLUP hierarchy. ## Nesting and mixing The shorthands compose. A `GROUP BY` list may contain plain columns alongside them, and multiple grouping constructs multiply out: ```sql GROUP BY tenant_id, ROLLUP(region, city) GROUP BY ROLLUP(year, month), CUBE(channel, device) ``` The first keeps `tenant_id` in every set; the second produces 3 × 4 = 12 grouping sets. Engines also allow parenthesised composite elements — `ROLLUP((country, state), city)` treats the pair as a single level so you never get a state without its country — though support for that spelling varies, so verify it against your engine's documentation. ## Portability `ROLLUP` and `CUBE` in the `GROUP BY (...)` form are the standard spellings and are accepted by PostgreSQL, Oracle, SQL Server and DB2. MySQL 8 offers only the trailing `WITH ROLLUP` syntax and has no CUBE at all; SQLite has neither. Where CUBE is unavailable, the fallback is a `UNION ALL` of the individual grouping queries — correct, but reading the input once per level.

Why is CUBE a poor choice over a date hierarchy like (year, month, day)?
Because CUBE would produce levels such as `(month)` and `(day)` without the year, which merge January 2024 with January 2025 and the 3rd of every month into one row. Those levels are arithmetically valid but meaningless as report lines, and they inflate the result. ROLLUP's prefixes give exactly year, year-month, year-month-day and the grand total.
What happens when a GROUP BY clause contains both ROLLUP and CUBE?
The grouping constructs multiply out: their grouping-set lists form a cross product. `GROUP BY ROLLUP(year, month), CUBE(channel, device)` yields 3 × 4 = 12 grouping sets, each pairing one date level with one channel/device level. It is a compact way to express a drill-down along one axis crossed with a full cross-tab on another — and an easy way to produce far more rows than intended.
How many rows does CUBE(a, b) return over a table with 2 distinct a values and 2 distinct b values where all four pairs occur?
Nine: four detail rows from `(a,b)`, two from `(a)`, two from `(b)`, and one grand total from `()`. In general each grouping set contributes as many rows as it has distinct value combinations in the data, so result size depends on both the number of sets and the cardinality of the columns.

saying these in an interview costs you the question

Says CUBE is just ROLLUP with the columns reversed
Thinks ROLLUP produces all combinations of the columns
Claims column order is irrelevant to ROLLUP
Uses CUBE over a date hierarchy and keeps the meaningless levels
States that every engine supports CUBE

How do GROUP BY ROLLUP(a, b, c) and GROUP BY CUBE(a, b, c) differ in the grouping sets they generate?

§ answer

basics

solid answer

code

§ go deeper

▾level notes4 levels

▾deep dive~3 min

Two shorthands over the same machinery

Column order matters for one and not the other

Which to reach for

Result size

Reading the output

Nesting and mixing

Portability

▾common follow-ups3 questions

▾red flags5 flags

§ context

▾related questions3 questions

▾related topics3 topics

Two shorthands over the same machinery

Column order matters for one and not the other

Which to reach for

Result size

Reading the output

Nesting and mixing

Portability

answer

go deeper

context