GROUP BY with ROLLUP and CUBE: Multi-Level Aggregation in SQL

1. Why GROUP BY ROLLUP does more than a plain GROUP BY

A classic reporting problem: a revenue report needs sums per region, per region and month, plus a grand total across all regions. With a plain GROUP BY, that requires several queries stitched together with UNION afterward, or an application that computes the subtotals itself. Exactly this problem is solved by GROUP BY ROLLUP directly inside the database, in a single statement, consistently and without duplicated logic.

ROLLUP is an extension of the GROUP BY clause that automatically produces subtotals and a grand total in addition to the normal grouping levels. The result is a single result set in which detail rows and subtotal rows sit side by side, distinguishable by NULL values in the grouping columns. Once you understand GROUP BY ROLLUP, you rarely need manual subtotal computation in the application layer for reporting queries again.

Related to ROLLUP are CUBE and GROUPING SETS, both defined in the SQL standard since SQL:1999 and supported by all major relational databases. All three extensions build on the same principle: instead of specifying a single fixed grouping level, you describe several levels at once, and the database merges multiple aggregation passes internally.

2. ROLLUP: producing hierarchical subtotals

GROUP BY ROLLUP expects an ordered list of columns and produces subtotals from right to left, from the most detailed to the coarsest level. With GROUP BY ROLLUP(region, category), three grouping levels emerge: region and category together, region alone, and finally the grand total across all rows. The order of the columns in ROLLUP is decisive, since it determines the hierarchy of the subtotals, not the sort order of the result.

Subtotal rows show NULL values for the columns no longer grouped at that aggregation level. That can be confusing when the source data itself contains genuine NULL values, for instance an unassigned category. The GROUPING function, discussed later, solves this reliably by distinguishing between a real NULL value and a summary NULL produced by ROLLUP.

-- Basic ROLLUP: subtotals per region, plus grand total
SELECT
    region,
    category,
    SUM(revenue) AS total_revenue
FROM sales
GROUP BY ROLLUP(region, category)
ORDER BY region NULLS LAST, category NULLS LAST;

-- Result rows include:
-- ('North', 'Electronics', 12000)
-- ('North', 'Furniture',    8000)
-- ('North', NULL,          20000)   -- subtotal for region 'North'
-- ('South', 'Electronics',  9500)
-- ('South', NULL,           9500)   -- subtotal for region 'South'
-- (NULL,    NULL,          29500)   -- grand total

A common use case for GROUP BY ROLLUP is financial reports with a hierarchical structure, such as year, quarter, month. With ROLLUP(year, quarter, month), the database automatically produces subtotals per year and quarter, plus a grand total, exactly in the structure expected in classic pivot reports. Without ROLLUP the same structure would need to be rebuilt with four separate GROUP BY queries and UNION ALL, which is more error prone and slower.

3. CUBE: every combination of groupings

While ROLLUP only produces hierarchical subtotals from right to left, CUBE goes one step further and delivers every possible combination of the given columns. With GROUP BY CUBE(region, category), four grouping levels emerge: region and category together, region alone, category alone, and the grand total. This extra combination, category alone without a region reference, is not delivered by ROLLUP, because ROLLUP is bound to the fixed order of the columns.

CUBE is particularly suited for exploratory analysis where it is not clear upfront which dimension is more relevant for the evaluation. A business analyst who wants to see both revenue sums per region and revenue sums per product category independently gets both views from CUBE in a single result, instead of writing two separate queries and merging them by hand.

-- CUBE: every combination of the given columns
SELECT
    region,
    category,
    SUM(revenue) AS total_revenue
FROM sales
GROUP BY CUBE(region, category);

-- Compared to ROLLUP, CUBE additionally returns:
-- (NULL,    'Electronics', 21500)  -- total per category, across all regions
-- (NULL,    'Furniture',    8000)

The downside of CUBE compared to ROLLUP is the number of result rows. With n columns, CUBE produces up to 2 to the power of n combinations, while ROLLUP delivers only n plus 1 rows per group. With three or four grouping columns, the result of CUBE can quickly become unwieldy, especially when the base table already contains many distinct values per column. Before using CUBE, it is always worth checking the cardinality of the columns involved.

4. GROUPING SETS: targeted control instead of automation

ROLLUP and CUBE follow fixed patterns, but not every report actually needs all hierarchical subtotals or all combinations. GROUPING SETS lets you list exactly the grouping levels you actually need, without the completeness forced by ROLLUP or CUBE. This is particularly useful when a report only needs region totals and category totals, but not the detail level of region plus category.

Technically, both ROLLUP and CUBE are just syntactic shortcuts for certain GROUPING SETS expressions. ROLLUP(a, b) corresponds to GROUPING SETS((a, b), (a), ()), and CUBE(a, b) corresponds to GROUPING SETS((a, b), (a), (b), ()). Once you know this equivalence, you can use GROUPING SETS deliberately to produce intermediate forms between ROLLUP and CUBE that neither shorthand directly represents.

-- GROUPING SETS: only the levels actually needed for the report
SELECT
    region,
    category,
    SUM(revenue) AS total_revenue
FROM sales
GROUP BY GROUPING SETS (
    (region, category),  -- detail level
    (region),            -- subtotal per region
    ()                    -- grand total, no per-category subtotal needed
);

In practice, GROUPING SETS pays off especially for reports with many columns, where CUBE would produce a combinatorial explosion of rows even though only a handful of specific combinations are actually needed. A well maintained GROUPING SETS query is often faster than a CUBE query with a subsequent filter on the relevant rows, because the database plans only the required aggregation passes from the start.

5. The GROUPING function: telling NULL apart from real values

A central problem with ROLLUP, CUBE, and GROUPING SETS is distinguishing a genuine NULL value in the data from a NULL value the database produced for a subtotal row. The function GROUPING(column) solves this reliably: it returns 1 if the column was rolled up at this aggregation level, and 0 if it is a real value from the source data, even if that value is NULL.

This lets you build a readable label for subtotal rows in the SELECT list, for instance with a CASE expression that substitutes the text Total or Subtotal whenever GROUPING equals 1. This matters especially when the result is displayed directly in a dashboard or export, without the application itself having to distinguish real NULL values from aggregation NULL values.

-- GROUPING() distinguishes real NULL from aggregation NULL
SELECT
    CASE WHEN GROUPING(region) = 1 THEN 'Total' ELSE region END AS region_label,
    CASE WHEN GROUPING(category) = 1 THEN 'All Categories' ELSE category END AS category_label,
    SUM(revenue) AS total_revenue,
    GROUPING(region) AS is_region_subtotal,
    GROUPING(category) AS is_category_subtotal
FROM sales
GROUP BY ROLLUP(region, category);

The related function GROUPING_ID(), available in PostgreSQL, SQL Server, and Oracle, combines several GROUPING calls into a single bit pattern and makes it easier to filter for specific aggregation levels, for instance only rows with exactly one subtotal. MySQL 8 has no native GROUPING_ID function, so multiple GROUPING calls must be combined manually there.

6. Practical example: a revenue report by region and category

A complete practical example shows how GROUP BY ROLLUP is used in a realistic reporting scenario. Suppose a company wants a quarterly report showing revenue by region and product category, with subtotals per region and a grand total at the end. Additionally, the average order value per grouping level should be included, which is easily possible within a single ROLLUP query.

Correct sorting matters for such reports: since ROLLUP produces subtotals with NULL values, the ORDER BY clause must explicitly state whether NULL values should sort first or last within each group, otherwise subtotals end up at a random position in the result. In PostgreSQL this is controlled with NULLS LAST, in MySQL and SQL Server via an additional CASE expression in the ORDER BY clause.

-- Complete quarterly report: subtotals per region, average order value
SELECT
    CASE WHEN GROUPING(region) = 1 THEN 'GRAND TOTAL' ELSE region END AS region,
    CASE WHEN GROUPING(category) = 1 THEN 'Subtotal' ELSE category END AS category,
    SUM(revenue) AS total_revenue,
    ROUND(AVG(order_value), 2) AS avg_order_value,
    COUNT(*) AS order_count
FROM sales
WHERE quarter = 'Q2-2026'
GROUP BY ROLLUP(region, category)
ORDER BY GROUPING(region), region, GROUPING(category), category;

An important note for application developers: WHERE conditions are applied before the ROLLUP computation, so they filter the base data, not the generated subtotal rows. Anyone wanting to display only certain subtotal rows, for instance only region totals without detail rows, must combine HAVING with GROUPING, since HAVING applies after aggregation and can therefore also be applied to the rows produced by ROLLUP.

7. Performance aspects on large tables

Internally, a database often executes GROUP BY ROLLUP by sorting or hashing the detail data once and then layering multiple aggregation passes over the same sorted set, instead of reading the base table multiple times. This is considerably more efficient than several separate GROUP BY queries with a subsequent UNION, since the most expensive operation, reading and sorting the base data, only runs once.

With CUBE the effort grows exponentially with the number of columns, since every additional combination means another logical aggregation pass. On tables with many millions of rows and more than three or four CUBE columns, execution time can increase noticeably. In such cases it is worth checking the execution plan with EXPLAIN and comparing whether GROUPING SETS with a reduced number of combinations fulfills the same business requirement with less effort.

An index on the grouping columns can speed up the sort based aggregation path, especially when the columns match the ROLLUP order. On very large fact tables in a data warehouse context, it is also sensible to maintain pre-aggregated materialized views for the most common ROLLUP combinations, instead of recomputing the full aggregation on every query.

8. Database differences: PostgreSQL, MySQL, SQL Server, Oracle

The syntax of ROLLUP, CUBE, and GROUPING SETS is defined in the SQL standard, but not all databases support it identically. PostgreSQL, SQL Server, and Oracle implement the full standard syntax including nested GROUPING SETS and GROUPING_ID. MySQL has supported ROLLUP since version 5.7 in an older syntax with WITH ROLLUP at the end of the query, and only from MySQL 8.0 onward also the standard syntax GROUP BY ROLLUP(...). CUBE and GROUPING SETS are still completely missing from MySQL as native syntax to this day.

Anyone needing CUBE in MySQL has to rebuild it manually via several UNION ALL queries with different GROUP BY levels, which recreates exactly the tedium that ROLLUP and CUBE are meant to avoid. For portable SQL meant to run on multiple databases, it is therefore worth checking GROUPING SETS as the lowest common denominator and testing its availability upfront, rather than blindly relying on CUBE.

9. Common mistakes and comparison table

The most common mistake with GROUP BY ROLLUP is the wrong column order. Writing ROLLUP(category, region) instead of ROLLUP(region, category) produces subtotals per category instead of per region, because ROLLUP aggregates from right to left. A second mistake is confusing genuine NULL values with aggregation NULL values in downstream filters, leading to wrong results when filtering without the GROUPING function. A third mistake is the unreflective use of CUBE with many columns, which can lead to an uncontrolled row explosion.

The following overview summarizes which tool fits which use case.

10. Summary

GROUP BY ROLLUP produces hierarchical subtotals and a grand total in a single statement, from the most detailed to the coarsest level, exactly in the order of the given columns. CUBE delivers every possible combination of the grouping columns and suits exploratory analysis, but brings an exponentially growing row count with many columns. GROUPING SETS allows a targeted selection of exactly the aggregation levels actually needed, and is technically the most general of the three forms.

The GROUPING function reliably solves the problem of distinguishing genuine NULL values from summary NULL values produced by ROLLUP or CUBE, and is a prerequisite for readable labels in reports. Choosing between ROLLUP, CUBE, and GROUPING SETS mainly depends on how many combinations are actually needed and how large the underlying table is. MySQL remains limited with CUBE and GROUPING SETS, which must be taken into account for portable SQL.

11. FAQ: GROUP BY ROLLUP and CUBE