SQL - ROLLUP with Examples

Key Insights

• ROLLUP generates hierarchical subtotals and grand totals automatically, eliminating the need for multiple queries or UNION ALL statements to build summary reports.
• Column order in ROLLUP matters—it processes from right to left, creating subtotals for each level of your hierarchy (year → quarter → month, not the reverse).
• Use GROUPING() functions to distinguish between actual NULL values in your data and the NULL placeholders that ROLLUP inserts for subtotal rows.

Introduction to ROLLUP

ROLLUP is a GROUP BY extension that generates subtotals and grand totals in a single query. Instead of writing multiple queries and combining them with UNION ALL, you get hierarchical aggregations automatically.

Consider a sales report. You want totals by region, then a grand total across all regions. Without ROLLUP, you’d write two queries and union them together. With ROLLUP, it’s one query that produces both levels.

The key concept is hierarchy. ROLLUP assumes your columns form a drill-down path: continent → country → city, or year → quarter → month. It produces subtotals at each level of that hierarchy, rolling up from the most granular to the most aggregate.

Use ROLLUP when your grouping columns have a natural parent-child relationship. If you need all possible combinations of subtotals (not just hierarchical ones), you’ll want CUBE instead—but that’s a different tool for a different job.

Basic Syntax and How ROLLUP Works

The syntax is straightforward:

SELECT column1, column2, aggregate_function(column3)
FROM table_name
GROUP BY ROLLUP(column1, column2);

ROLLUP creates grouping sets from right to left. For ROLLUP(a, b, c), you get:

1. Groups by (a, b, c) — most detailed
2. Groups by (a, b) — subtotal for each a, b combination
3. Groups by (a) — subtotal for each a
4. Groups by () — grand total

Here’s a concrete example. Suppose you have a sales table:

CREATE TABLE sales (
    region VARCHAR(50),
    amount DECIMAL(10, 2)
);

INSERT INTO sales VALUES
('North', 1500.00),
('North', 2300.00),
('South', 1800.00),
('South', 2100.00),
('East', 1200.00),
('West', 2800.00);

A basic ROLLUP on region:

SELECT 
    region,
    SUM(amount) AS total_sales
FROM sales
GROUP BY ROLLUP(region);

Result:

The NULL in the region column indicates the grand total row. That single query replaced what would have been a regular GROUP BY plus a separate total query.

Multi-Column ROLLUP

When you add multiple columns to ROLLUP, the hierarchy becomes more interesting. Column order determines the subtotal structure.

CREATE TABLE detailed_sales (
    region VARCHAR(50),
    category VARCHAR(50),
    amount DECIMAL(10, 2)
);

INSERT INTO detailed_sales VALUES
('North', 'Electronics', 1500.00),
('North', 'Electronics', 800.00),
('North', 'Clothing', 600.00),
('South', 'Electronics', 2100.00),
('South', 'Clothing', 900.00),
('South', 'Clothing', 400.00);

Now ROLLUP with two columns:

SELECT 
    region,
    category,
    SUM(amount) AS total_sales
FROM detailed_sales
GROUP BY ROLLUP(region, category)
ORDER BY region NULLS LAST, category NULLS LAST;

Result:

You get three levels: detail rows (region + category), region subtotals (region + NULL), and the grand total (NULL + NULL).

Swap the column order to ROLLUP(category, region) and you’d get category subtotals instead of region subtotals. The first column in ROLLUP is the outermost grouping level.

Partial ROLLUP

Sometimes you want subtotals for only part of your grouping. Partial ROLLUP lets you mix standard GROUP BY columns with ROLLUP columns.

CREATE TABLE time_sales (
    year INT,
    quarter INT,
    month INT,
    amount DECIMAL(10, 2)
);

INSERT INTO time_sales VALUES
(2024, 1, 1, 5000.00),
(2024, 1, 2, 5500.00),
(2024, 1, 3, 4800.00),
(2024, 2, 4, 6200.00),
(2024, 2, 5, 5900.00),
(2024, 2, 6, 6100.00);

To get subtotals within the time hierarchy but always grouped by year:

SELECT 
    year,
    quarter,
    month,
    SUM(amount) AS total_sales
FROM time_sales
GROUP BY year, ROLLUP(quarter, month)
ORDER BY year, quarter NULLS LAST, month NULLS LAST;

Result:

Notice there’s no row where year is NULL. The year column stays in standard GROUP BY, so you get quarterly subtotals and a yearly total, but no grand total across all years.

Using GROUPING() and GROUPING_ID()

NULL values in ROLLUP output are placeholders meaning “all values.” But what if your data contains actual NULLs? The GROUPING() function solves this ambiguity.

GROUPING(column) returns 1 if the column is aggregated (a subtotal row), 0 if it’s a regular grouped value:

SELECT 
    CASE WHEN GROUPING(region) = 1 THEN 'All Regions' ELSE region END AS region,
    CASE WHEN GROUPING(category) = 1 THEN 'All Categories' ELSE category END AS category,
    SUM(amount) AS total_sales,
    GROUPING(region) AS region_is_subtotal,
    GROUPING(category) AS category_is_subtotal
FROM detailed_sales
GROUP BY ROLLUP(region, category)
ORDER BY GROUPING(region), region, GROUPING(category), category;

Result:

GROUPING_ID() combines multiple GROUPING() results into a single integer, useful for filtering or sorting:

SELECT 
    region,
    category,
    SUM(amount) AS total_sales,
    GROUPING_ID(region, category) AS grouping_level
FROM detailed_sales
GROUP BY ROLLUP(region, category);

The grouping_level values: 0 = detail, 1 = region subtotal, 3 = grand total. It’s a bitmap where each bit represents whether that column is aggregated.

ROLLUP vs. CUBE vs. GROUPING SETS

Three related operators, three different use cases:

• ROLLUP: Hierarchical subtotals (drill-down path)
• CUBE: All possible subtotal combinations
• GROUPING SETS: Explicit control over which groupings to include

Here’s the same data with all three:

-- ROLLUP: hierarchical (region → category)
SELECT region, category, SUM(amount)
FROM detailed_sales
GROUP BY ROLLUP(region, category);
-- Produces: (region, category), (region), ()

-- CUBE: all combinations
SELECT region, category, SUM(amount)
FROM detailed_sales
GROUP BY CUBE(region, category);
-- Produces: (region, category), (region), (category), ()

-- GROUPING SETS: explicit control
SELECT region, category, SUM(amount)
FROM detailed_sales
GROUP BY GROUPING SETS(
    (region, category),
    (region),
    ()
);
-- Produces exactly what you specify

CUBE adds a subtotal by category alone—something ROLLUP doesn’t provide because category isn’t a parent of region in the hierarchy. Use CUBE when dimensions are independent (like analyzing sales by both region and product line without a hierarchy). Use GROUPING SETS when you need specific combinations that don’t fit either pattern.

Practical Use Cases

Here’s a complete financial report query that combines everything:

CREATE TABLE financial_data (
    fiscal_year INT,
    quarter INT,
    department VARCHAR(50),
    expense_type VARCHAR(50),
    amount DECIMAL(12, 2)
);

INSERT INTO financial_data VALUES
(2024, 1, 'Engineering', 'Salaries', 150000.00),
(2024, 1, 'Engineering', 'Equipment', 25000.00),
(2024, 1, 'Marketing', 'Salaries', 80000.00),
(2024, 1, 'Marketing', 'Advertising', 45000.00),
(2024, 2, 'Engineering', 'Salaries', 155000.00),
(2024, 2, 'Engineering', 'Equipment', 15000.00),
(2024, 2, 'Marketing', 'Salaries', 82000.00),
(2024, 2, 'Marketing', 'Advertising', 52000.00);

SELECT 
    fiscal_year,
    CASE 
        WHEN GROUPING(quarter) = 1 THEN 'Annual'
        ELSE 'Q' || quarter::VARCHAR
    END AS period,
    CASE 
        WHEN GROUPING(department) = 1 THEN 'COMPANY TOTAL'
        ELSE department
    END AS department,
    CASE 
        WHEN GROUPING(expense_type) = 1 THEN 'Subtotal'
        ELSE expense_type
    END AS expense_type,
    TO_CHAR(SUM(amount), 'FM$999,999,999.00') AS total_amount,
    CASE GROUPING_ID(quarter, department, expense_type)
        WHEN 0 THEN 'Detail'
        WHEN 1 THEN 'Dept Quarter Total'
        WHEN 3 THEN 'Quarter Total'
        WHEN 7 THEN 'Grand Total'
        ELSE 'Subtotal'
    END AS row_type
FROM financial_data
GROUP BY fiscal_year, ROLLUP(quarter, department, expense_type)
ORDER BY 
    fiscal_year,
    GROUPING(quarter), quarter,
    GROUPING(department), department,
    GROUPING(expense_type), expense_type;

This produces a report with quarterly breakdowns by department and expense type, quarterly totals, and an annual grand total—all from one query. The GROUPING_ID helps downstream applications identify row types for formatting or filtering.

ROLLUP shines in reporting scenarios: financial statements, inventory aging reports, time-series analytics with period comparisons, and any hierarchical summary where you need multiple aggregation levels without multiple queries.