🧊 SQL `CUBE` – Generate All Possible Subtotals in One Query

The CUBE operator in SQL allows you to perform multidimensional aggregation, giving you every possible subtotal combination of the specified columns—including the grand total.

It’s a powerful tool for business intelligence, pivot-style reports, and cross-dimensional summaries.

📘 What is SQL CUBE?

SQL CUBE is a grouping extension that builds on GROUP BY, generating subtotals for all combinations of a group of columns.

Instead of writing multiple queries or using nested UNIONs, CUBE gives you all necessary groupings in one simple query.

🧾 SQL CUBE Syntax

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

column1, column2: Dimensions to aggregate across
The query returns:
- Aggregation by both columns
- Subtotals for each individual column
- Grand total

✅ Example: Sales Summary with All Subtotals

Let’s say you have a sales table like:

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

You want to see sales by:

Region + Year
Region only
Year only
All data combined (grand total)

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

🧾 Result:

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

🎯 Breakdown:

Region + Year → standard grouping
Region + NULL → subtotal for region
NULL + Year → subtotal for year
NULL + NULL → grand total

🧠 Pro Tip: Use `GROUPING()` to Differentiate NULLs

Since CUBE introduces NULL for subtotal levels, use the GROUPING() function to check whether a value is actual NULL or subtotal NULL.

SELECT 
  region,
  year,
  SUM(amount) AS total_sales,
  GROUPING(region) AS is_region_total,
  GROUPING(year) AS is_year_total
FROM sales
GROUP BY CUBE (region, year);

💼 Real-Life Use Cases for SQL CUBE

📊 Business dashboards with drilldowns
🧾 Financial reports by department, category, year
🛒 Sales summaries across region, product, and time
👥 HR analytics across role, location, department

🔍 SQL CUBE vs ROLLUP vs GROUPING SETS

Feature	`CUBE`	`ROLLUP`	`GROUPING SETS`
All combinations	✅ Yes	❌ Only hierarchical	✅ Custom combinations
Grand total	✅ Included	✅ Included	✅ Optional
Subtotals	✅ All directions	✅ One direction	✅ Custom
Performance	🐢 Can be slow on large datasets	⚡ Fast	⚠ Balanced
Use case	OLAP, multidimensional reporting	Hierarchical totals	Targeted aggregations

📝 Summary

SQL CUBE generates all combinations of grouped subtotals
Ideal for BI reports, dashboards, and OLAP-style analysis
Use GROUPING() to identify which rows are totals or subtotals
Powerful, but be cautious with performance on large data

CUBE

🧊 SQL CUBE – Generate All Possible Subtotals in One Query