Microsoft SQL Server GROUP BY GROUP BY with ROLLUP and CUBE

Help us to keep this website almost Ad Free! It takes only 10 seconds of your time:
> Step 1: Go view our video on YouTube: EF Core Bulk Extensions
> Step 2: And Like the video. BONUS: You can also share it!

Example

The ROLLUP operator is useful in generating reports that contain subtotals and totals.

  • CUBE generates a result set that shows aggregates for all combinations of values in the selected columns.

  • ROLLUP generates a result set that shows aggregates for a hierarchy of values in the selected columns.

    ItemColorQuantity
    TableBlue124
    TableRed223
    ChairBlue101
    ChairRed210
SELECT CASE WHEN (GROUPING(Item) = 1) THEN 'ALL'
            ELSE ISNULL(Item, 'UNKNOWN')
       END AS Item,
       CASE WHEN (GROUPING(Color) = 1) THEN 'ALL'
            ELSE ISNULL(Color, 'UNKNOWN')
       END AS Color,
       SUM(Quantity) AS QtySum
FROM Inventory
GROUP BY Item, Color WITH ROLLUP

Item                 Color                QtySum                     
-------------------- -------------------- -------------------------- 
Chair                Blue                 101.00                     
Chair                Red                  210.00                     
Chair                ALL                  311.00                     
Table                Blue                 124.00                     
Table                Red                  223.00                     
Table                ALL                  347.00                     
ALL                  ALL                  658.00 

(7 row(s) affected)

If the ROLLUP keyword in the query is changed to CUBE, the CUBE result set is the same, except these two additional rows are returned at the end:

ALL                  Blue                 225.00                     
ALL                  Red                  433.00 

https://technet.microsoft.com/en-us/library/ms189305(v=sql.90).aspx



Got any Microsoft SQL Server Question?