SQL ROLLUP & CUBE – Subtotals and Cross-Tabulations

ROLLUP Syntax

ROLLUP is specified inside the GROUP BY clause:

SQL

-- Standard SQL syntax (supported by PostgreSQL, MySQL 8+, SQL Server, Oracle)
SELECT column1, column2, aggregate_function(column3)
FROM   table_name
GROUP BY ROLLUP(column1, column2);

ℹ️

ROLLUP order matters

The column order inside ROLLUP defines the grouping hierarchy. ROLLUP(dept, job) gives dept+job rows, dept subtotals, and a grand total — rolling up from right to left. Changing the order changes which subtotals are generated.

ROLLUP Example

Sales data grouped by department and job title with department subtotals and a grand total:

SQL

SELECT
    department,
    job_title,
    SUM(salary)  AS total_salary,
    COUNT(*)     AS headcount
FROM employees
GROUP BY ROLLUP(department, job_title)
ORDER BY department, job_title;

▶ Rows produced by ROLLUP

Notice that the subtotal rows use NULL to indicate "all values" for the rolled-up column. The GROUPING() function (below) helps distinguish these NULLs from actual NULL data.

CUBE Syntax and Example

CUBE generates subtotals for every possible combination of the listed columns:

SQL

SELECT
    department,
    region,
    SUM(sales_amount) AS total_sales
FROM sales
GROUP BY CUBE(department, region)
ORDER BY department, region;

▶ Groups produced by CUBE(department, region)

(department, region) ← detail rows
(department, NULL) ← subtotal per department across all regions
(NULL, region) ← subtotal per region across all departments
(NULL, NULL) ← grand total

For N columns, CUBE generates 2^N grouping combinations. With 3 columns that is 8 combinations. CUBE is ideal for multi-dimensional analysis (e.g., pivot tables).

Feature	ROLLUP	CUBE
Groups generated	N+1 (hierarchical)	2^N (all combinations)
Best for	Hierarchical reports (year › month › day)	Multi-dimensional analysis (pivot-style)
Column order matters?	Yes	No

The GROUPING() Function

ROLLUP and CUBE use NULL to mark rolled-up positions. But what if your real data also contains NULLs? The GROUPING() function distinguishes them:

SQL

SELECT
    CASE WHEN GROUPING(department) = 1 THEN '** ALL DEPTS **'
         ELSE department END             AS department,
    CASE WHEN GROUPING(job_title) = 1  THEN '** ALL JOBS **'
         ELSE job_title END              AS job_title,
    SUM(salary)  AS total_salary,
    COUNT(*)     AS headcount
FROM employees
GROUP BY ROLLUP(department, job_title)
ORDER BY GROUPING(department), department, GROUPING(job_title), job_title;

GROUPING(col) returns 1 when the NULL in that column was added by ROLLUP/CUBE (i.e., it represents "all values"), and 0 for actual data rows (including real NULLs in the source data).

Use Cases

💡

Where ROLLUP and CUBE shine

Financial reports with subtotals by region, quarter, and product line; dashboards showing both detail and summary rows in a single query; data warehousing fact tables where BI tools expect pre-rolled-up data.

SQL

-- Quarterly revenue report with yearly subtotals and grand total
SELECT
    CASE WHEN GROUPING(fiscal_year)    = 1 THEN 'ALL YEARS'
         ELSE CAST(fiscal_year AS VARCHAR) END AS fiscal_year,
    CASE WHEN GROUPING(fiscal_quarter) = 1 THEN 'ANNUAL TOTAL'
         ELSE CONCAT('Q', fiscal_quarter) END  AS fiscal_quarter,
    SUM(revenue)  AS total_revenue
FROM quarterly_sales
GROUP BY ROLLUP(fiscal_year, fiscal_quarter)
ORDER BY fiscal_year, fiscal_quarter;

Summary

📋 Summary

ROLLUP generates N+1 grouping levels rolling up from right to left — detail rows, subtotals per leading column(s), and a grand total.
CUBE generates subtotals for every combination of grouped columns (2^N groups) — ideal for multi-dimensional analysis.
Both use NULL to represent "all values" in rolled-up positions.
GROUPING(col) returns 1 for ROLLUP/CUBE-added NULLs and 0 for real data — use it with CASE to label summary rows.
Use ROLLUP for hierarchical (drill-down) reports; use CUBE for pivot-style cross-tabulations.

FAQ

Is ROLLUP supported in all major databases? +

Yes. ROLLUP is part of the SQL:1999 standard and is supported by PostgreSQL, MySQL 8+, SQL Server, Oracle, and SQLite (partial). MySQL 5.x used a non-standard syntax GROUP BY col1, col2 WITH ROLLUP — if targeting MySQL, verify your version supports the standard syntax.

Can I use HAVING with ROLLUP? +

Yes. HAVING filters groups after ROLLUP/CUBE generates them. Be careful: HAVING may remove grand total or subtotal rows if they do not meet the condition. Use HAVING GROUPING(col) = 1 OR aggregate_condition if you want to keep summary rows regardless.

What is GROUPING SETS and how does it relate to ROLLUP and CUBE? +

GROUPING SETS lets you specify exactly which grouping combinations to compute — it is the most general form. ROLLUP and CUBE are shorthand for specific patterns of GROUPING SETS. For example, ROLLUP(a, b) is equivalent to GROUPING SETS((a,b), (a), ()). Use GROUPING SETS when you need a custom combination that is neither a pure ROLLUP nor CUBE.