SQL Quest › SQL Interview Questions › Rollup questions
SQL rollup interview questions: subtotals, grand totals, ROLLUP and CUBE
A rollup question asks for one aggregate at several levels in one result: a row per group, a subtotal per parent group, and a grand total. PostgreSQL, SQL Server, Oracle and Snowflake write it as GROUP BY ROLLUP, CUBE or GROUPING SETS. SQLite has none of the three, so each question below is worked both ways.
The data is SQL Quest's own practice tables: an online shop's orders and a company's employees with their managers. Every result table on this page is what the SQLite query returns on them.
ROLLUP, CUBE and GROUPING SETS
| Clause | Groups by | What you get |
|---|---|---|
| GROUP BY ROLLUP (a, b) | (a, b), (a), () | Detail rows, a subtotal per a, and the grand total. The order of the columns sets the hierarchy. |
| GROUP BY CUBE (a, b) | (a, b), (a), (b), () | Every combination: subtotals in both directions, like the margins of a cross-tab. |
| GROUP BY GROUPING SETS ((a), (b), ()) | (a), (b), () | Exactly the groupings you list, and nothing else. |
Which databases have them
| Database | Rollup syntax |
|---|---|
| PostgreSQL, SQL Server, Oracle, Snowflake | ROLLUP, CUBE, GROUPING SETS and GROUPING(), written as on this page. |
| MySQL | Writes ROLLUP as GROUP BY a, b WITH ROLLUP. |
| SQLite | None of them. Build each level with its own GROUP BY and stack the levels with UNION ALL. SQL Quest runs SQLite in your browser, so every result table on this page comes from the UNION ALL form. |
On a subtotal row the column that was rolled away is NULL. Label it with CASE WHEN GROUPING(col) = 1 THEN 'All' ELSE col END rather than COALESCE: a column can hold real NULLs too, and a GROUP BY manager_id over this table already has a NULL group of 7 people. GROUPING() is 1 only on the rows the rollup added.
Five rollup interview questions, worked
1.Revenue per category, with a grand total row
From orders, show the revenue of completed orders for each category, and one more row with the revenue of all categories together. Put the total last.
PostgreSQL · the GROUP BY clause is the same in SQL Server, Oracle and Snowflake
SELECT CASE WHEN GROUPING(category) = 1 THEN 'All categories'
ELSE category END AS category,
ROUND(SUM(total), 2) AS revenue
FROM orders
WHERE status = 'completed'
GROUP BY ROLLUP (category)
ORDER BY GROUPING(category), category;
SQLite has no ROLLUP: the same result with UNION ALL · runs in SQL Quest
WITH done AS (
SELECT category, total FROM orders WHERE status = 'completed'
)
SELECT category, revenue FROM (
SELECT category, ROUND(SUM(total), 2) AS revenue, 0 AS is_total
FROM done
GROUP BY category
UNION ALL
SELECT 'All categories', ROUND(SUM(total), 2), 1
FROM done
)
ORDER BY is_total, category;
Returns, on the ecommerce tables:
| category | revenue |
|---|---|
| Electronics | 4,799.75 |
| Furniture | 3,171.76 |
| All categories | 7,971.51 |
ROLLUP with one column adds exactly one row: the group-by-nothing row, whose category is NULL. GROUPING(category) is 1 on that row and 0 on the others, which is how the query labels it and sorts it last.
The SQLite form says the same thing in two halves: one SELECT per level, stacked with UNION ALL. The filter lives in the CTE so both halves read the same 37 completed orders; filter only one half and the total stops matching the rows above it.
2.Monthly revenue by category, with a subtotal per month
Show completed-order revenue for each month and category, a subtotal row after each month, and a grand total at the bottom.
PostgreSQL · the GROUP BY clause is the same in SQL Server, Oracle and Snowflake
WITH m AS (
SELECT SUBSTR(order_date, 1, 7) AS month, category, total
FROM orders
WHERE status = 'completed'
)
SELECT CASE WHEN GROUPING(month) = 1 THEN 'All months' ELSE month END AS month,
CASE WHEN GROUPING(category) = 1 THEN 'All categories'
ELSE category END AS category,
ROUND(SUM(total), 2) AS revenue
FROM m
GROUP BY ROLLUP (month, category)
ORDER BY GROUPING(month), month, GROUPING(category), category;
SQLite has no ROLLUP: the same result with UNION ALL · runs in SQL Quest
WITH m AS (
SELECT SUBSTR(order_date, 1, 7) AS month, category, total
FROM orders
WHERE status = 'completed'
)
SELECT month, category, revenue FROM (
SELECT month, category, ROUND(SUM(total), 2) AS revenue, 0 AS lvl
FROM m GROUP BY month, category
UNION ALL
SELECT month, 'All categories', ROUND(SUM(total), 2), 1
FROM m GROUP BY month
UNION ALL
SELECT 'All months', 'All categories', ROUND(SUM(total), 2), 2
FROM m
)
ORDER BY lvl = 2, month, lvl, category;
Returns, on the ecommerce tables:
| month | category | revenue |
|---|---|---|
| 2024-01 | Electronics | 2,749.88 |
| 2024-01 | Furniture | 1,721.93 |
| 2024-01 | All categories | 4,471.81 |
| 2024-02 | Electronics | 2,049.87 |
| 2024-02 | Furniture | 1,449.83 |
| 2024-02 | All categories | 3,499.70 |
| All months | All categories | 7,971.51 |
ROLLUP (month, category) is a hierarchy read left to right: (month, category), then (month), then (). So you get a subtotal per month but no subtotal per category across months. Swap the two columns and you get the other hierarchy.
Three levels means three SELECTs in SQLite. The lvl column is what keeps each subtotal under its own month and the grand total at the bottom; sorting on the label text alone would put "All categories" above "Electronics".
3.The same report with totals in both directions
Now the report also needs each category's revenue across both months, as a cross-tab would show in its bottom margin.
PostgreSQL · the GROUP BY clause is the same in SQL Server, Oracle and Snowflake
WITH m AS (
SELECT SUBSTR(order_date, 1, 7) AS month, category, total
FROM orders
WHERE status = 'completed'
)
SELECT CASE WHEN GROUPING(month) = 1 THEN 'All months' ELSE month END AS month,
CASE WHEN GROUPING(category) = 1 THEN 'All categories'
ELSE category END AS category,
ROUND(SUM(total), 2) AS revenue
FROM m
GROUP BY CUBE (month, category)
ORDER BY GROUPING(month), month, GROUPING(category), category;
SQLite has no CUBE: the same result with UNION ALL · runs in SQL Quest
WITH m AS (
SELECT SUBSTR(order_date, 1, 7) AS month, category, total
FROM orders
WHERE status = 'completed'
)
SELECT month, category, revenue FROM (
SELECT month, category, ROUND(SUM(total), 2) AS revenue, 0 AS g_month, 0 AS g_cat
FROM m GROUP BY month, category
UNION ALL
SELECT month, 'All categories', ROUND(SUM(total), 2), 0, 1
FROM m GROUP BY month
UNION ALL
SELECT 'All months', category, ROUND(SUM(total), 2), 1, 0
FROM m GROUP BY category
UNION ALL
SELECT 'All months', 'All categories', ROUND(SUM(total), 2), 1, 1
FROM m
)
ORDER BY g_month, month, g_cat, category;
Returns, on the ecommerce tables:
| month | category | revenue |
|---|---|---|
| 2024-01 | Electronics | 2,749.88 |
| 2024-01 | Furniture | 1,721.93 |
| 2024-01 | All categories | 4,471.81 |
| 2024-02 | Electronics | 2,049.87 |
| 2024-02 | Furniture | 1,449.83 |
| 2024-02 | All categories | 3,499.70 |
| All months | Electronics | 4,799.75 |
| All months | Furniture | 3,171.76 |
| All months | All categories | 7,971.51 |
CUBE over two columns is four groupings: both, month only, category only, none. That is two more rows than the ROLLUP above, the per-category totals, and nothing else changes.
In SQLite each grouping is one more UNION ALL branch, so CUBE over n columns costs 2ⁿ branches. The two flag columns play the part of GROUPING(): they sort the rows and never appear in the output.
4.Headcount by department and by hire year, in one result
From employees, return headcount and payroll per department, per hire year, and for the whole company, in one result, without the department-by-year combinations.
PostgreSQL · the GROUP BY clause is the same in SQL Server, Oracle and Snowflake
WITH e AS (
SELECT department, SUBSTR(hire_date, 1, 4) AS hire_year, salary
FROM employees
)
SELECT CASE WHEN GROUPING(department) = 0 THEN 'department'
WHEN GROUPING(hire_year) = 0 THEN 'hire_year'
ELSE 'total' END AS dimension,
CASE WHEN GROUPING(department) = 0 THEN department
WHEN GROUPING(hire_year) = 0 THEN hire_year
ELSE 'All employees' END AS grp,
COUNT(*) AS headcount,
SUM(salary) AS payroll
FROM e
GROUP BY GROUPING SETS ((department), (hire_year), ())
ORDER BY dimension, grp;
SQLite has no GROUPING SETS: the same result with UNION ALL · runs in SQL Quest
WITH e AS (
SELECT department, SUBSTR(hire_date, 1, 4) AS hire_year, salary
FROM employees
)
SELECT 'department' AS dimension, department AS grp,
COUNT(*) AS headcount, SUM(salary) AS payroll
FROM e GROUP BY department
UNION ALL
SELECT 'hire_year', hire_year, COUNT(*), SUM(salary)
FROM e GROUP BY hire_year
UNION ALL
SELECT 'total', 'All employees', COUNT(*), SUM(salary)
FROM e
ORDER BY dimension, grp;
Returns, on the employees tables:
| dimension | grp | headcount | payroll |
|---|---|---|---|
| department | Engineering | 18 | 1,567,000 |
| department | Finance | 7 | 534,000 |
| department | HR | 8 | 479,000 |
| department | Marketing | 8 | 490,000 |
| department | Sales | 9 | 568,000 |
| hire_year | 2016 | 1 | 80,000 |
| hire_year | 2017 | 4 | 403,000 |
| hire_year | 2018 | 5 | 485,000 |
| hire_year | 2019 | 10 | 842,000 |
| hire_year | 2020 | 12 | 826,000 |
| hire_year | 2021 | 11 | 640,000 |
| hire_year | 2022 | 7 | 362,000 |
| total | All employees | 50 | 3,638,000 |
GROUPING SETS is the general form: ROLLUP and CUBE are shorthands for particular lists of sets. Here neither fits, because the two dimensions are not a hierarchy and the cross of them is not wanted.
The two groupings share no column, so a dimension column says which one each row belongs to. In SQLite the three branches can be sorted directly, because the ORDER BY of a UNION ALL may name its output columns.
5.A rollup up a tree: payroll under each top-level manager
employees.manager_id points at each person's manager. For every person who reports to no one, show how many people sit in their tree (themselves included), the tree's payroll, and how many levels it goes down.
SQLite and PostgreSQL · runs in SQL Quest
WITH RECURSIVE org AS (
SELECT emp_id, name AS head, salary, 0 AS depth
FROM employees
WHERE manager_id IS NULL
UNION ALL
SELECT e.emp_id, org.head, e.salary, org.depth + 1
FROM employees e
JOIN org ON e.manager_id = org.emp_id
)
SELECT head, COUNT(*) AS people, SUM(salary) AS payroll, MAX(depth) AS levels
FROM org
GROUP BY head
ORDER BY payroll DESC;
Returns, on the employees tables:
| head | people | payroll | levels |
|---|---|---|---|
| Eva Martinez | 17 | 1,452,000 | 2 |
| Paul Thompson | 7 | 534,000 | 1 |
| Carol Williams | 8 | 490,000 | 1 |
| Jack Anderson | 8 | 479,000 | 1 |
| Grace Lee | 8 | 468,000 | 1 |
| Ulysses Cook | 1 | 115,000 | 0 |
| Jason Roberts | 1 | 100,000 | 0 |
ROLLUP totals up the columns you group by. A hierarchy stored as a parent pointer has no column for "everyone under this person", so the tree has to be walked first: the anchor starts at the 7 people with no manager, and each round adds the next level down while carrying the head's name along. After that it is an ordinary GROUP BY.
The trees do not match the departments. Engineering has 18 people but Eva Martinez's tree has 17, because Ulysses Cook reports to no one; Sales has 9 and Grace Lee's tree 8, because Jason Roberts reports to no one either. Both answers are right; they answer different questions, so say which one the interviewer asked for.
This query runs unchanged in PostgreSQL. SQL Server and Oracle write plain WITH, without RECURSIVE. Rolling up to every manager at every level, not only the top, is the harder version: practise it on Recursive Team Size Rollup below.
Practise it
Questions from the bank on a share of the grand total and on manager hierarchies. Their solutions stay in the app.
Work out a share of the grand total, against a checked answer
SQLite in your browser, no signup. Your rows are compared with the expected rows, and a wrong answer gets a diagnosis, not just "incorrect".
Practise in the browser editor →Not sure which skill to work on? Take the ten-question readiness test.
Questions
What is ROLLUP in SQL?
An extension of GROUP BY that adds subtotal rows and a grand total to the same result. GROUP BY ROLLUP (a, b) groups by (a, b), then by (a) alone, then by nothing, and stacks the three. The columns rolled away are NULL on the subtotal rows.
What is the difference between ROLLUP and CUBE?
ROLLUP follows the column order as a hierarchy: for (a, b) it gives (a, b), (a) and the total. CUBE gives every combination: (a, b), (a), (b) and the total. With two columns CUBE adds the subtotals for b across all values of a.
Does SQLite support ROLLUP?
No. SQLite has no ROLLUP, CUBE or GROUPING SETS. Write one SELECT per level, each with its own GROUP BY, stack them with UNION ALL, and add a column that sorts the total rows where you want them.
How do I tell a subtotal row from a real NULL?
Use GROUPING(column): it is 1 when the column was rolled away on that row and 0 when the row really groups by it. COALESCE(column, 'Total') is not safe, because a column that already holds NULLs gets two rows that look the same: a GROUP BY manager_id over this table already has a NULL group of 7 people.
How do I write a rollup in MySQL?
GROUP BY a, b WITH ROLLUP gives the same levels as GROUP BY ROLLUP (a, b) in PostgreSQL, SQL Server, Oracle and Snowflake.
How do I total up a hierarchy such as an org chart?
ROLLUP cannot do it, because the levels are rows linked by a parent column, not columns. Walk the tree with a recursive CTE that carries the root along, then GROUP BY the root, as in the last question on this page.