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.

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!

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);

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.)

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.