🔢 SQL `ROLLUP` – Generate Subtotals and Grand Totals in SQL

The SQL ROLLUP operator helps you create hierarchical summaries by automatically calculating subtotals and grand totals in your GROUP BY queries.

It’s widely used in reporting and dashboarding tasks where you want multiple levels of aggregation without writing multiple queries or using UNION ALL.

📘 What is SQL ROLLUP?

The ROLLUP operator is a grouping extension that generates aggregated rows at multiple levels of a hierarchy.

🎯 Use it when you want:

Subtotals at intermediate levels
A grand total for all data
To avoid redundant GROUP BY + UNION combinations

🧾 Syntax of SQL ROLLUP

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

The order of columns in the ROLLUP matters – it creates nested subtotals from left to right.

✅ Example: Region and Yearly Sales with Subtotals

Let’s say you have the following sales table:

region	year	amount
East	2023	500
East	2024	700
West	2023	600

The following query uses ROLLUP to generate subtotals:

SELECT region, year, SUM(amount) AS total_sales
FROM sales
GROUP BY ROLLUP (region, year);

🧾 Result:

region	year	total_sales
East	2023	500
East	2024	700
East	NULL	1200
West	2023	600
West	NULL	600
NULL	NULL	1800

📌 Interpretation:

Rows with NULL in year → subtotal by region
Row with NULL in both → grand total

🧠 How ROLLUP Works Internally

ROLLUP (A, B) generates:

Group by A and B
Group by A only (subtotal)
Grand total (no grouping)

More columns = more subtotal levels.

🧠 Pro Tip: Use `GROUPING()` Function

You can distinguish real NULLs from subtotal NULLs using the GROUPING() function:

SELECT 
  region,
  year,
  SUM(amount) AS total_sales,
  GROUPING(region) AS region_grouped,
  GROUPING(year) AS year_grouped
FROM sales
GROUP BY ROLLUP (region, year);

This helps you format subtotal/grand total rows differently in your reports.

🧮 Real-World Use Cases for SQL ROLLUP

📊 Sales reports with region-wise and total summaries
🧾 Expense summaries by department, then grand total
👥 Employee headcounts per team, plus org-level totals
🏬 Inventory reports by category and product

🔍 SQL ROLLUP vs GROUPING SETS vs CUBE

Feature	`ROLLUP`	`GROUPING SETS`	`CUBE`
Subtotals	✅ Yes (hierarchical)	✅ Yes (custom)	✅ Yes (all combinations)
Grand total	✅ Yes	✅ Optional	✅ Yes
Custom logic	❌ Fixed hierarchy	✅ Fully customizable	❌ Generates every possibility
Performance	⚡ Fast	⚠ Depends on sets	🐢 Slow on large datasets

📝 Summary

ROLLUP simplifies hierarchical summarization
Use it to automatically generate subtotals and grand totals
Combine it with GROUPING() for formatting and clarity
Perfect for BI dashboards, sales summaries, and financial reports

ROLLUP

🔢 SQL ROLLUP – Generate Subtotals and Grand Totals in SQL