ROLLUP and grouping sets

This lesson uses the unmodified DDI_SQL_Training dataset v1.0.0 on SQL Server 2019 or later. Add a grand total to grouped order counts without confusing it with a NULL source value.

Run the example

SELECT CASE WHEN GROUPING(OrderStatus) = 1 THEN N'All statuses'
            ELSE OrderStatus END AS StatusGroup,
       COUNT(*) AS OrderCount
FROM dbo.Orders
GROUP BY ROLLUP (OrderStatus)
ORDER BY GROUPING(OrderStatus), OrderStatus;

Read it step by step

1. ROLLUP(OrderStatus) produces one group per status plus a grand-total group.
2. GROUPING(OrderStatus) distinguishes the grand-total marker from an actual NULL status.
3. ORDER BY places the individual statuses before the total row.

Check the result

The status counts sum to the grand total; the source OrderStatus column is NOT NULL.

Practice

Count orders by OrderChannel and add a grand total using GROUPING SETS.

SELECT CASE WHEN GROUPING(OrderChannel) = 1 THEN N'All channels'
            ELSE OrderChannel END AS ChannelGroup,
       COUNT(*) AS OrderCount
FROM dbo.Orders
GROUP BY GROUPING SETS ((OrderChannel), ())
ORDER BY GROUPING(OrderChannel), OrderChannel;