Subtotals and Grand Totals With ROLLUP and GROUPING SETS

A report needs totals by region, by product, and for the whole business. Three GROUP BY queries joined with UNION ALL work, but GROUPING SETS can express the same result in one statement. ROLLUP and CUBE generate related subtotal patterns; GROUPING_ID tells you which subtotal row you are reading.

Create a Small Report Example

Use a tiny table with two regions and two products so the result can be checked by eye. Amount is decimal, not float, because money-like totals should be exact at the chosen scale. The table includes a NULL region to show why a displayed NULL is not enough to identify a subtotal row.

DROP TABLE IF EXISTS #Sales;
CREATE TABLE #Sales
(
    Region varchar(20) NULL,
    Product varchar(20) NOT NULL,
    Amount decimal(12,2) NOT NULL
);
INSERT #Sales VALUES
('East','Book',100.00),('East','Course',80.00),
('West','Book',60.00),('West','Course',120.00),
(NULL,'Book',20.00);
SELECT * FROM #Sales;

I write the expected grand total, 380.00, before testing any grouping syntax. What does a NULL region mean in the source: unknown geography or a subtotal? Both can display as NULL, so the query needs a structural label.

Use ROLLUP for a Hierarchy

ROLLUP(Region, Product) returns detail groups, a subtotal for each region, and one grand total. It follows the order of grouping expressions. It does not return product totals across all regions. That makes it useful for a hierarchy such as year, quarter, month, but incomplete for the report requested here.

SELECT Region, Product, SUM(Amount) AS total_amount,
       GROUPING_ID(Region, Product) AS group_level
FROM #Sales
GROUP BY ROLLUP (Region, Product)
ORDER BY GROUPING_ID(Region, Product), Region, Product;

The GROUPING_ID result is 0 for a detail row, 1 when Product is rolled up, and 3 for the grand total. A real NULL Region detail row remains distinguishable because its grouping bit is not set. Do not label every NULL as Total with COALESCE alone; that would mislabel source data.

See What CUBE Adds

CUBE(Region, Product) returns detail, region subtotals, product subtotals, and a grand total. It covers the requested totals but also emits detail rows. With many grouping columns, the number of grouping combinations grows quickly, so CUBE can create far more output than a report needs.

SELECT Region, Product, SUM(Amount) AS total_amount,
       GROUPING_ID(Region, Product) AS group_level
FROM #Sales
GROUP BY CUBE (Region, Product)
ORDER BY group_level, Region, Product;

A CUBE result can be useful for an exploratory pivot, but it is not automatically the most efficient or clearest form. If the report requires only three levels, state those levels directly. That also makes review easier: the SQL lists the intended grain of every subtotal.

Select Exactly Three Grouping Sets

GROUPING SETS ((Region), (Product), ()) asks for region totals, product totals, and the grand total. The empty grouping set is the grand total. GROUPING_ID labels them as 1, 2, and 3 respectively when arguments are (Region, Product). Use those values or the GROUPING function to make human-readable labels.

SELECT CASE GROUPING_ID(Region, Product)
         WHEN 1 THEN 'Region total'
         WHEN 2 THEN 'Product total'
         WHEN 3 THEN 'Grand total' END AS row_type,
       CASE WHEN GROUPING(Region)=1 THEN 'All regions'
            ELSE COALESCE(Region,'Unknown region') END AS region_label,
       CASE WHEN GROUPING(Product)=1 THEN 'All products'
            ELSE Product END AS product_label,
       SUM(Amount) AS total_amount
FROM #Sales
GROUP BY GROUPING SETS ((Region), (Product), ());

The source NULL Region is labeled Unknown region, while a rolled-up Region is labeled All regions. That distinction matters when the report is reconciled to source rows. Keep numeric totals and labels separate; a string label should not replace a nullable key in downstream data processing.

Compare GROUPING SETS With UNION ALL

The familiar version scans or aggregates the source for each branch. It can be easy to understand but repeats logic and invites one branch to use a different WHERE filter. Write the three branches with identical predicates and compare the actual plan with the single-statement version. A small temp table will not produce meaningful timing; use representative data.

SET STATISTICS IO ON;
SELECT 'Region total' AS row_type, Region, NULL AS Product,
       SUM(Amount) AS total_amount
FROM #Sales GROUP BY Region
UNION ALL
SELECT 'Product total', NULL, Product, SUM(Amount)
FROM #Sales GROUP BY Product
UNION ALL
SELECT 'Grand total', NULL, NULL, SUM(Amount)
FROM #Sales;
SET STATISTICS IO OFF;

Run the earlier labeled query under the same IO setting and inspect reads, sorts, hash aggregates, memory grants, and spills. SQL Server can choose several aggregation strategies; do not assume one physical scan from the syntax alone. Verify exact row types and totals in both outputs before comparing cost.

Validate Report Semantics

A region total and a product total overlap, so summing every row in the output double-counts sales. Consumers need row_type or grouping ID to select the level they want. A dashboard can display all levels together, but its grand total must come from the grand-total row, not from adding subtotals. Include a test for source NULLs and any filters used by the report.

I keep the sample's 380.00 grand total as a regression check. When a new product or unknown region is added, the three levels should still reconcile to the base table. GROUPING SETS makes the intended grain explicit; the validation keeps that convenience from becoming a misleading report.

Read GROUPING_ID as Bits

GROUPING_ID encodes which expressions were rolled up. With two arguments, the rightmost expression contributes the low bit. In (Region, Product), a region subtotal has Product rolled up and returns 1. A product subtotal has Region rolled up and returns 2, and the grand total has both rolled up and returns 3. This is more reliable than testing Region IS NULL, because a real source row can have a NULL region.

If you change the order of arguments in GROUPING_ID, the numeric labels change. Keep the expression list in the SELECT aligned with the grouping list and use CASE labels that a reviewer can verify from a small sample. I store a test with a real NULL key specifically to catch accidental relabeling.

Filter Before Aggregation

A report filter should apply to the source rows before all subtotal levels are calculated. Filtering the final result on a displayed Region can remove a grand total or produce a misleading subtotal. Put date and status predicates in WHERE, then generate the subtotal levels. If different subtotal levels need different filters, they are not the same aggregation problem and a UNION ALL design with explicit branches can be clearer.

A grand total produced from the filtered source can be compared with a plain SUM over the same WHERE clause. That independent check catches a missing date predicate or a duplicate join. The grouping syntax does not protect against joining one sale to several dimension rows before aggregating. Verify source grain first.

Watch the GROUPING SETS Plan Shape at Scale

An optimizer can use a sort, hash aggregate, or spool depending on row count and indexes. GROUPING SETS can avoid repeated logical reads, but it can also ask for a larger memory grant than three small grouped branches. Capture actual plan and spill warnings under peak data volume. Compare output row counts and exact totals first, then reads and CPU.

I keep UNION ALL as a readable baseline. Replacing it with one statement is a good simplification only when the new plan and labels meet the report's contract.

Related reading on this blog: Introduction to Rollup Clause and Single Table Scan for Multiple Aggregated Operators.

A subtotal is not self-explanatory, it is clear when grouping metadata labels its level.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.