Mastering ROLLUP, CUBE, and GROUPING SETS in SQL

A common requirement in reporting is to show data at multiple levels of granularity: sales by region, sales by product, and a grand total of all sales.

Traditionally, you might use UNION ALL to combine three separate GROUP BY queries. This is verbose, hard to maintain, and inefficient (scanning the table three times).

Enter the SQL standard extensions for GROUP BY: ROLLUP, CUBE, and GROUPING SETS. These allow you to generate multiple levels of aggregation in a single query pass.

A Real Business Scenario: One Query for Detail Rows, Subtotals, and Grand Totals

Reporting teams often need one result set that serves several levels of a dashboard at once:

• detail by region and product
• subtotal by region
• grand total for the whole business

Without grouping extensions, people often write multiple GROUP BY queries and glue them together with UNION ALL. That works, but it is verbose and easy to drift out of sync as the report evolves.

ROLLUP, CUBE, and GROUPING SETS matter because they let the database express the aggregation design directly instead of making the application reconstruct the hierarchy after the fact.

1. ROLLUP: Hierarchical Aggregation

ROLLUP is designed for hierarchical data (e.g., Year > Month > Day, or Country > State > City). It generates subtotals moving up the hierarchy.

If you group by ROLLUP(Category, Product), you get:

1. Sales by Category and Product
2. Sales by Category (Subtotal)
3. Grand Total

SELECT
    COALESCE(category, 'Total') as category,
    COALESCE(product, 'All Products') as product,
    SUM(sales) as total_sales
FROM sales_data
GROUP BY ROLLUP(category, product);

(Note: COALESCE is often used to replace the NULL values generated by the rollup with readable labels like 'Total'.)

2. CUBE: All Possible Combinations

CUBE generates subtotals for every possible combination of the grouping columns. It is useful for cross-tabular reports (matrices) where there is no strict hierarchy.

If you group by CUBE(Color, Size), you get:

1. Sales by Color and Size
2. Sales by Color (All Sizes)
3. Sales by Size (All Colors)
4. Grand Total

Be careful: CUBE generates $2^N$ grouping references, so it can return a huge number of rows if you abuse it!

A Common Mistake: Replacing Every NULL with "Total" Too Early

In grouping-extension output, NULL can mean two very different things:

• the original data actually had a null dimension value
• the row is a subtotal or grand total produced by the grouping extension

If you blindly write:

COALESCE(category, 'Total')

you may erase that distinction and mislabel real null data as a subtotal. That is exactly why GROUPING(column) exists.

3. GROUPING SETS: Precision Control

GROUPING SETS is the most flexible. It lets you specify exactly which levels of aggregation you want.

SELECT category, product, SUM(sales)
FROM sales_data
GROUP BY GROUPING SETS (
    (category, product), -- Detailed
    (category),          -- Category Subtotals
    ()                   -- Grand Total
);

This is equivalent to ROLLUP, but you can choose to skip levels (e.g., show Grand Total and Product totals, but not Category totals).

The GROUPING() Function

When you see a NULL in a ROLLUP result, does it mean "Grand Total" or does it mean the column actually contained a null value?

The GROUPING(column) function solves this ambiguity. It returns 1 if the row is a subtotal for that column (super-aggregate) and 0 if it's a regular value.

SELECT
    CASE WHEN GROUPING(category) = 1 THEN 'Grand Total' ELSE category END as category,
    SUM(sales) as total_sales
FROM sales_data
GROUP BY ROLLUP(category);

Boundary and Performance Notes

These extensions are powerful, but they change result shape and row count in ways that reporting pipelines need to handle carefully.

• CUBE expands quickly as dimensions increase, so the output can become much larger than expected.
• Sorting and aggregating across multiple grouping levels can still be expensive on large fact tables.
• Consumers of the result need a reliable way to distinguish detail rows from subtotal rows, usually with GROUPING() or explicit level labels.
• Not every engine implements the exact same syntax or support level, so portability needs to be checked before relying on advanced grouping extensions.

In practice, GROUPING SETS is often the safest default when you know exactly which subtotal levels you need and want to avoid the combinatorial expansion of CUBE.

Interactive Playground

Note: SQLite (which powers this playground) has limited support for these extensions compared to PostgreSQL or SQL Server. However, we can simulate GROUPING SETS using UNION ALL to demonstrate the concept of multi-level aggregation.

(Pro Tip: In reporting databases like Snowflake, BigQuery, or Postgres, always prefer the native GROUP BY ROLLUP syntax over UNION ALL for performance.)

When NOT to Use ROLLUP, CUBE, or GROUPING SETS

Avoid these extensions when:

• the report consumer cannot distinguish subtotal rows from detail rows cleanly
• you only need one or two simple aggregates and multiple-level output adds confusion
• portability to engines with weak support matters more than the elegance of one native query

Sometimes two explicit grouped queries are easier to maintain than one clever result set that mixes five levels of aggregation.

Official References

• PostgreSQL grouping sets documentation for formal ROLLUP, CUBE, and GROUPING SETS semantics.
• SQL Server GROUP BY documentation for another major-engine implementation of advanced grouping.
• BigQuery GROUP BY GROUPING SETS documentation for warehouse-style reporting usage.

Conclusion

Mastering these extensions separates SQL novices from professionals. They allow you to push aggregation logic down to the database layer, where it belongs, keeping your application code cleaner and your reports faster.

Related Articles

• Mastering SQL GROUP BY: From Basics to Advanced Aggregations for the core grouping model that ROLLUP and GROUPING SETS extend.
• How to Create Pivot Tables in SQL for another reporting pattern that turns grouped results into multi-level summaries.
• Conditional Aggregation in SQL: A Complete Guide for the CASE-based report-building technique that often complements advanced grouping extensions.