SQL ROLLUP

ROLLUP is an extension of the GROUP BY clause that generates subtotals and a grand total in the result set. It helps in generating hierarchical reports by computing aggregate values at multiple levels.

 

Syntax of SQL ROLLUP

SELECT column_name1, column_name2, aggregate_function(column_name)  
FROM table_name  
GROUP BY ROLLUP(column_name1, column_name2);  

• column_name1, column_name2: The columns used for grouping.
• aggregate_function(): Functions like SUM(), COUNT(), AVG(), MAX(), or MIN().
• ROLLUP(): Computes subtotals for each level and a grand total.

 

Key Features of SQL ROLLUP

• Computes subtotals for each level of grouping.
• Computes the grand total for all rows.
• Produces hierarchical results useful for reports.
• The order of columns in ROLLUP() affects the output structure.

 

Example of SQL ROLLUP

Consider the following table:

Table: Sales

Query: Compute total sales for each category and product, including subtotals

SELECT category, product, SUM(sales_amount) AS total_sales  
FROM sales  
GROUP BY ROLLUP(category, product);  

Output:

category	product	total_sales
Electronics	Laptop	1000
Electronics	Phone	700
Electronics	NULL	1700
Furniture	Chair	500
Furniture	Table	800
Furniture	NULL	1300
NULL	NULL	3000

Explanation:

1. Group by category and product: Computes total sales per product.
2. Subtotal for each category: NULL in product means a subtotal for that category.
3. Grand total: NULL in both category and product represents the total sales for all categories.

 

SQL ROLLUP vs. GROUPING SETS vs. CUBE

Feature	ROLLUP	CUBE	GROUPING SETS
Generates subtotals	✅	✅	✅
Generates grand total	✅	✅	✅
Generates all possible combinations	❌	✅	✅ (customizable)
Best for hierarchical data	✅	❌	✅

 

SQL ROLLUP with COUNT()

Example: Count the number of products in each category and overall

SELECT category, product, COUNT(*) AS product_count  
FROM sales  
GROUP BY ROLLUP(category, product);  

 

Summary

• ROLLUP is used for hierarchical reports with subtotals and grand totals.
• The NULL values represent subtotal or total rows.
• The order of columns in ROLLUP() affects grouping levels.
• Use cases: Financial reports, sales summaries, and hierarchical aggregations.