What do GROUP BY ROLLUP and CUBE do? | Interview Question

Q: What do GROUP BY ROLLUP and CUBE do?

**Target Audience:** Beginners who know `GROUP BY` and want subtotals and grand totals in one query, and candidates preparing for reporting and analytics questions. **Difficulty:** Medium **Engines:** PostgreSQL 16.15 for `ROLLUP`, `CUBE` and `GROUPING SETS`; SQLite 3.45.0 (sql.js 1.10.2, the engine the runnable example on this page uses) for the portable `UNION ALL` version, because SQLite has no `ROLLUP`. Both were run for this page. MySQL and SQL Server notes are cited. > **How to read this doc:** Every idea is explained in plain language first, then tagged with `📌 Interview term:` so you also learn the exact words an interviewer expects. Every result and error message shown was produced by actually running it; the **Verified** section quotes the example output in full. ## 1. Why This Even Matters — A Story First At the end of the month a shop manager prints the sales sheet. Under each region she wants the products listed, then a line "North total", then the next region, and at

The problem

Question presented to candidate: "I want a sales report with a total per region and product, a subtotal per region, and a grand total, all in one result. What do GROUP BY ROLLUP and GROUP BY CUBE do, how are they different, and how do you tell a subtotal row from a real NULL in the data?"

What a strong answer should cover:

Both are shorthands for grouping sets: one query that aggregates the same rows at several levels and returns all levels in one result, as if several GROUP BY queries were glued with UNION ALL
ROLLUP (a, b) = sets (a, b), (a), (): a hierarchy of subtotals, n + 1 sets for n columns, so column order matters
CUBE (a, b) = every combination (a, b), (a), (b), (): 2^n sets, for cross-tab reports
In subtotal rows the rolled-up columns are NULL; GROUPING(col) returns 1 there and 0 for a NULL that is really in the data, which is how you label and sort the rows
Engine support: PostgreSQL (since 9.5) and SQL Server have all three; MySQL has only WITH ROLLUP; SQLite has none, so you emulate with UNION ALL

Clarifying questions expected:

Which subtotal levels are needed: a hierarchy (region, then product within region) or every cross-combination?
Can the grouping columns themselves contain NULL in the data?

Code / implementation expected: Yes — a ROLLUP query with GROUPING() used to label subtotal rows, and the UNION ALL equivalent.

Schema used by the examples:

group-by-rollup

Try it yourself

Sketch your approach before you read the solution. That is what the interview tests.

Stuck? Get a hint.

A short nudge toward the approach. It will not give the answer away.

Solution

Target Audience: Beginners who know GROUP BY and want subtotals and grand totals in one query, and candidates preparing for reporting and analytics questions. Difficulty: Medium Engines: PostgreSQL 16.15 for ROLLUP, CUBE and GROUPING SETS; SQLite 3.45.0 (sql.js 1.10.2, the engine the runnable example on this page uses) for the portable UNION ALL version, because SQLite has no ROLLUP. Both were run for this page. MySQL and SQL Server notes are cited.

How to read this doc: Every idea is explained in plain language first, then tagged with 📌 Interview term: so you also learn the exact words an interviewer expects. Every result and error message shown was produced by actually running it; the Verified section quotes the example output in full.

1. Why This Even Matters — A Story First

At the end of the month a shop manager prints the sales sheet. Under

The solution is hidden so you can try first

13 min read · 12 sections

Run the code

Edit the query and run it against the tables for this question.

SQLSQLite (no ROLLUP): the same report as ROLLUP built from three GROUP BYs and UNION ALL

PostgreSQL: GROUP BY ROLLUP with GROUPING() flags on every row

SELECT region, product, SUM(amount) AS total,
       GROUPING(region)  AS g_region,
       GROUPING(product) AS g_product
FROM sales
GROUP BY ROLLUP (region, product)
ORDER BY GROUPING(region), region, GROUPING(product), product;

PostgreSQL: GROUP BY CUBE with readable labels built from GROUPING()

SELECT CASE WHEN GROUPING(region) = 1 THEN 'ALL REGIONS'
            ELSE COALESCE(region, '(no region)') END AS region_label,
       CASE WHEN GROUPING(product) = 1 THEN 'ALL PRODUCTS'
            ELSE product END AS product_label,
       SUM(amount) AS total
FROM sales
GROUP BY CUBE (region, product)
ORDER BY GROUPING(region), region, GROUPING(product), product;

PostgreSQL: GROUPING SETS lists exactly the levels you want

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

Discussion

Other approaches, follow-ups and how it went in real interviews.

Sign in to join the discussion.

No comments yet. Share how you would answer it.

The solution is hidden so you can try first

Keep your momentum going.