SQL ROLLUP

SQL ROLLUP

The ROLLUP operator in SQL is an extension of the GROUP BY clause. It enables hierarchical or cumulative subtotals and a grand total in the result set. It's often used for generating summary reports with multiple levels of aggregation.

Why Use SQL ROLLUP?

• Quickly compute subtotals and totals for hierarchical groupings.
• Simplifies complex aggregation queries.
• Ideal for reports and data summaries that require grouped and cumulative views.

Syntax

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

Key Points

1. Hierarchical Aggregation:
   • Aggregates at each level of the specified columns, starting from the most detailed to the overall total.
2. NULLs Indicate Totals:
   • Rows with NULL values represent subtotals or grand totals.
3. Fewer Combinations than CUBE:
   • Unlike CUBE, ROLLUP does not calculate all combinations—just cumulative groupings.

Example Dataset

Sales Table

Region	Product	Sales
North	A	100
North	B	200
South	A	150
South	B	250

Examples

1. Using ROLLUP for Region and Product

SELECT Region, Product, SUM(Sales) AS TotalSales
FROM Sales
GROUP BY ROLLUP (Region, Product);

Result:

Region	Product	TotalSales
North	A	100
North	B	200
North	NULL	300
South	A	150
South	B	250
South	NULL	400
NULL	NULL	700

2. Using GROUPING Function

To distinguish subtotals and totals from regular rows, use the GROUPING() function. It returns 1 for grouped columns and 0 for detailed rows.

SELECT 
    Region,
    Product,
    SUM(Sales) AS TotalSales,
    GROUPING(Region) AS IsRegionGrouped,
    GROUPING(Product) AS IsProductGrouped
FROM Sales
GROUP BY ROLLUP (Region, Product);

Result:

Region	Product	TotalSales	IsRegionGrouped	IsProductGrouped
North	A	100	0	0
North	B	200	0	0
North	NULL	300	0	1
South	A	150	0	0
South	B	250	0	0
South	NULL	400	0	1
NULL	NULL	700	1	1

3. Filtering Subtotals and Totals

You can filter the result set to include only specific levels of aggregation using the GROUPING() function.

SELECT Region, Product, SUM(Sales) AS TotalSales
FROM Sales
GROUP BY ROLLUP (Region, Product)
HAVING GROUPING(Product) = 1; -- Include rows where Product is grouped

Result:

Region	Product	TotalSales
North	NULL	300
South	NULL	400
NULL	NULL	700

How ROLLUP Works

For GROUP BY ROLLUP (Region, Product), SQL generates results for:

1. (Region, Product) – Detailed grouping.
2. (Region) – Subtotal for each Region.
3. () – Grand total (no grouping).

Difference Between ROLLUP and CUBE

Feature	ROLLUP	CUBE
Aggregation Levels	Hierarchical (cumulative)	All combinations
Use Case	Totals and subtotals	Full multi-dimensional analysis
Complexity	Simpler	More comprehensive

Equivalent Query Without ROLLUP

Without ROLLUP, you would need to write multiple GROUP BY queries and combine their results using UNION ALL:

-- Detailed rows
SELECT Region, Product, SUM(Sales) AS TotalSales
FROM Sales
GROUP BY Region, Product
UNION ALL
-- Subtotals for Region
SELECT Region, NULL AS Product, SUM(Sales) AS TotalSales
FROM Sales
GROUP BY Region
UNION ALL
-- Grand total
SELECT NULL AS Region, NULL AS Product, SUM(Sales) AS TotalSales
FROM Sales;

Using ROLLUP simplifies this process into a single query.

Common Use Cases

1. Sales Reports:
   • Generate sales subtotals for regions and products, along with a grand total.
2. Financial Summaries:
   • Aggregate revenue or expenses at different hierarchical levels.
3. Inventory Reports:
   • Summarize stock levels by category, subcategory, and overall.

Performance Considerations

1. Indexes: Proper indexing can improve performance when using ROLLUP.
2. Data Size: Large datasets with multiple levels of aggregation may impact query performance.
3. Database Support: Supported in major databases like SQL Server, PostgreSQL, Oracle, and MySQL (v8.0+).

Conclusion

The SQL ROLLUP operator is a powerful tool for generating hierarchical data summaries, making it ideal for reporting and business intelligence. Its ability to calculate subtotals and totals with minimal effort simplifies complex queries.