ROLLUP
SQL · Grouping Rows
Theory
ROLLUP is a shorthand for a hierarchy of grouping sets. GROUP BY ROLLUP (dept, role) produces the groupings (dept, role), (dept) and (): detail rows for every department and role, a subtotal row for each department, and one grand total. With n columns it creates n + 1 levels, rolling up from right to left.
Column order matters because ROLLUP assumes the first column is the top of the hierarchy, like year then month or country then city. Subtotal rows show NULL in the rolled-up columns; use GROUPING(col) to label them as 'Total'. PostgreSQL, SQL Server, Oracle and Snowflake support ROLLUP (...); MySQL writes it as GROUP BY dept, role WITH ROLLUP; SQLite has no ROLLUP, so you emulate it with UNION ALL.
Why we use it
Financial and sales reports need subtotals per category plus a grand total, exactly like a spreadsheet with subtotal rows. ROLLUP produces them in one pass instead of several queries.
When to use it
Use ROLLUP when your grouping columns form a natural hierarchy (year > month, region > city). If you need every combination, such as per role across all departments, use CUBE; for hand-picked levels use GROUPING SETS.
How it works
- GROUP BY ROLLUP (dept, role)
- Group by (dept, role): detail rows
- Group by (dept): one subtotal per department
- Group by (): one grand total row
- Mark rolled-up columns as NULL
- Return details, subtotals and the total together
Syntax
SELECT c1, c2, AGG(c3)
FROM table
GROUP BY ROLLUP (c1, c2);Example
SELECT department, role, COUNT(*)
FROM employees
GROUP BY ROLLUP (department, role);Key points
- Assumes a hierarchical relationship between columns, left to right
- Great for reporting subtotal rows
- ROLLUP of n columns produces n + 1 grouping levels
- MySQL syntax: GROUP BY a, b WITH ROLLUP
- Use GROUPING(col) to tell subtotal NULLs from real NULLs