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

ClauseGroups byWhat 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

DatabaseRollup syntax
PostgreSQL, SQL Server, Oracle, SnowflakeROLLUP, CUBE, GROUPING SETS and GROUPING(), written as on this page.
MySQLWrites ROLLUP as GROUP BY a, b WITH ROLLUP.
SQLiteNone 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:

categoryrevenue
Electronics4,799.75
Furniture3,171.76
All categories7,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:

monthcategoryrevenue
2024-01Electronics2,749.88
2024-01Furniture1,721.93
2024-01All categories4,471.81
2024-02Electronics2,049.87
2024-02Furniture1,449.83
2024-02All categories3,499.70
All monthsAll categories7,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:

monthcategoryrevenue
2024-01Electronics2,749.88
2024-01Furniture1,721.93
2024-01All categories4,471.81
2024-02Electronics2,049.87
2024-02Furniture1,449.83
2024-02All categories3,499.70
All monthsElectronics4,799.75
All monthsFurniture3,171.76
All monthsAll categories7,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:

dimensiongrpheadcountpayroll
departmentEngineering181,567,000
departmentFinance7534,000
departmentHR8479,000
departmentMarketing8490,000
departmentSales9568,000
hire_year2016180,000
hire_year20174403,000
hire_year20185485,000
hire_year201910842,000
hire_year202012826,000
hire_year202111640,000
hire_year20227362,000
totalAll employees503,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:

headpeoplepayrolllevels
Eva Martinez171,452,0002
Paul Thompson7534,0001
Carol Williams8490,0001
Jack Anderson8479,0001
Grace Lee8468,0001
Ulysses Cook1115,0000
Jason Roberts1100,0000

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.

Each Salary's Share of PayrollEasy · SQL practice questionTransaction Share by Merchant CategoryEasy · SQL practice questionManagement Hierarchy OverviewMedium · SQL practice questionRecursive Team Size RollupHard · SQL practice questionRecursive Org Chart TraversalHard · SQL practice question

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.

Related

Recursive CTEs explainedAnchor, step, stop, on an org chart GROUP BY tutorialThe grain of a result Average of averagesWhy a total is not the mean of the subtotals' means Aggregation practiceGROUP BY and HAVING questions CTE practiceChained and recursive CTE questions SQL practice questionsBy topic and difficulty