SQL Server › Aggregate Functions & GROUP BY

How do the ROLLUP and CUBE extensions to GROUP BY help you generate subtotal and grand total rows in a single query?

Advanced

Answer

ROLLUP generates subtotal rows for each level of a hierarchy along with a final grand total, while CUBE generates subtotals for every possible combination of the grouped columns, both saving you from writing several separate queries and manually combining their results to build a complete summary report.

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

Real-world example A regional sales report uses ROLLUP to show sales totals by product category within each region, subtotals for each region overall, and a single grand total row for the entire company, all in one query.

Common follow-ups: How do you distinguish a subtotal row from a regular detail row in the result set?;What is the performance impact of using CUBE on a very large table?

Window Functions;Query Optimization & Plans