Comparing GROUP BY / ROLLUP / GROUPING SETS / CUBE in SQL
In Microsoft SQL Server / Azure SQL / Fabric Warehouse, these three operators are extensions of GROUP BY.
Their purpose: Calculate subtotals and grand totals in one query, without writing multiple UNIONs.
Sample Data
| OrderYear | ProductName | Qty |
|---|---|---|
| 2023 | A | 10 |
| 2023 | B | 20 |
| 2024 | A | 15 |
| 2024 | B | 25 |
1) GROUP BY (Standard Baseline)
SELECT OrderYear, ProductName, SUM(Qty) AS TotalQty
FROM Sales
GROUP BY OrderYear, ProductName;
Result:
| OrderYear | ProductName | TotalQty |
|---|---|---|
| 2023 | A | 10 |
| 2023 | B | 20 |
| 2024 | A | 15 |
| 2024 | B | 25 |
This is standard GROUP BY with no subtotals.
2) ROLLUP(OrderYear, ProductName)
SELECT OrderYear, ProductName, SUM(Qty) AS TotalQty
FROM Sales
GROUP BY ROLLUP(OrderYear, ProductName);
Result:
| OrderYear | ProductName | TotalQty |
|---|---|---|
| 2023 | A | 10 |
| 2023 | B | 20 |
| 2023 | NULL | 30 ← 2023 subtotal |
| 2024 | A | 15 |
| 2024 | B | 25 |
| 2024 | NULL | 40 ← 2024 subtotal |
| NULL | NULL | 70 ← grand total |
ROLLUP = Year subtotals + grand total for all columns
3) GROUPING SETS
SELECT OrderYear, ProductName, SUM(Qty) AS TotalQty
FROM Sales
GROUP BY GROUPING SETS
(
(OrderYear, ProductName), -- Detail level
(OrderYear), -- Year subtotals
(ProductName) -- Product subtotals
);
Result:
| OrderYear | ProductName | TotalQty |
|---|---|---|
| 2023 | A | 10 |
| 2023 | B | 20 |
| 2023 | NULL | 30 |
| 2024 | A | 15 |
| 2024 | B | 25 |
| 2024 | NULL | 40 |
| NULL | A | 25 ← Product A total |
| NULL | B | 45 ← Product B total |
GROUPING SETS = Choose which subtotals you want No mandatory grand total
4) CUBE(OrderYear, ProductName)
SELECT OrderYear, ProductName, SUM(Qty) AS TotalQty
FROM Sales
GROUP BY CUBE(OrderYear, ProductName);
Result:
| OrderYear | ProductName | TotalQty |
|---|---|---|
| 2023 | A | 10 |
| 2023 | B | 20 |
| 2023 | NULL | 30 |
| 2024 | A | 15 |
| 2024 | B | 25 |
| 2024 | NULL | 40 |
| NULL | A | 25 |
| NULL | B | 45 |
| NULL | NULL | 70 ← grand total |
CUBE = All possible combinations = GROUPING SETS(detail + year subtotals + product subtotals + grand total)
Quick Summary
| Operator | Description |
|---|---|
| ROLLUP | Hierarchical subtotals from left to right |
| GROUPING SETS | Choose specific subtotal combinations you want |
| CUBE | All possible combinations - complete subtotals |