Querying and aggregation
ROLLUP, CUBE and GROUPING SETS
Detail rows, subtotals and grand totals.
Understand the concept
GROUPING SETS produces several aggregation levels in one query. ROLLUP adds hierarchical subtotals; CUBE creates totals for all grouping combinations. CUBE output can grow rapidly with more dimensions, so explicitly requesting the levels you need is often clearer.
Do not confuse subtotal NULL markers with actual NULL data. GROUPING(Column) identifies whether a column was omitted for an aggregate level. A reporting layer can use that signal to label total rows correctly.
Use it to show category totals and a grand total in one report.
Labelling every NULL as a total using COALESCE alone.
One topic. Three levels of depth.
SELECT Category, COUNT(*) AS Products FROM lab.Product GROUP BY ROLLUP(Category);Adds the grand total of seven products to category counts.
This page does not execute SQL. Practise in the downloadable SqlStudioLab using SSMS. Before rerunning temporary-table examples, remove only the relevant #table. Views, procedures and functions create persistent lab objects; never use a production connection.
Try it yourself first.
Return customer counts by city and a grand total.
Show the solution and explanation
SELECT City, COUNT(*) AS Customers, GROUPING(City) AS IsTotal FROM lab.Customer GROUP BY ROLLUP(City);The grand total is 6; GROUPING distinguishes data NULLs from total markers.