CUBE  is an extension of the GROUP BY clause that generates all possible subtotal combinations for the specified columns.

Think of CUBE as:

GROUP BY on every possible combination of columns + grand total

1.Why use CUBE?

Without CUBE, you must write multiple GROUP BY queries to get totals and subtotals.

With CUBE, SQL Server does it automatically.

2.Basic Syntax

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

3.Example Table: Sales

Year Region Amount
2024 Asia 1000
2024 Europe 1500
2025 Asia 2000
2025 Europe 2500

4.Normal GROUP BY

SELECT Year, Region, SUM(Amount) AS TotalSales
FROM Sales
GROUP BY Year, Region;

✔ Only detailed totals

5.Using GROUP BY CUBE

SELECT Year, Region, SUM(Amount) AS TotalSales
FROM Sales
GROUP BY CUBE (Year, Region);

6.Result Explanation

Year Region TotalSales Meaning
2024 Asia 1000 Year + Region
2024 Europe 1500 Year + Region
2025 Asia 2000 Year + Region
2025 Europe 2500 Year + Region
2024 NULL 2500 Year total
2025 NULL 4500 Year total
NULL Asia 3000 Region total
NULL Europe 4000 Region total
NULL NULL 7000 Grand total

🔹 NULL means subtotal or grand total

7.Using GROUPING() to Identify Totals

SELECT
Year,
Region,
SUM(Amount) AS TotalSales,
GROUPING(Year) AS IsYearTotal,
GROUPING(Region) AS IsRegionTotal
FROM Sales
GROUP BY CUBE (Year, Region);

Categorized in:

SQL Server,