Summarizing Data Using ROLLUP and CUBE

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

For example, a simple table Inventory contains the following:
Item Color Quantity
-------------------- -------------------- --------------------------
Table Blue 124
Table Red 223
Chair Blue 101
Chair Red 210

This query generates a subtotal report:

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

Following are the specific differences between CUBE and ROLLUP:

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.