Summarizing Data Using ROLLUP
The ROLLUP operator is useful in generating reports that contain subtotals and totals. The ROLLUP operator generates a result set that is similar to the result sets generated by the CUBE operator. For more information, see Summarizing Data Using CUBE.
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.
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
The CUBE operation generated rows for possible combinations of values from both Item and Color. For example, not only does CUBE report all possible combinations of Color values combined with the Item value Chair (Red, Blue, and Red + Blue), it also reports all possible combinations of Item values combined with the Color value Red (Chair, Table, and Chair + Table).
For each value in the columns on the right in the GROUP BY clause, the ROLLUP operation does not report all possible combinations of values from the column, or columns, on the left. For example, ROLLUP does not report all the possible combinations of Item values for each Color value.
The result set of a ROLLUP operation has functionality similar to that returned by a COMPUTE BY. However, ROLLUP has the following advantages:
- ROLLUP returns a single result set while COMPUTE BY returns multiple result sets that increase the complexity of application code.
- ROLLUP can be used in a server cursor while COMPUTE BY cannot.
- The query optimizer can sometimes generate more efficient execution plans for ROLLUP than it can for COMPUTE BY.
See Also
Concepts
Summarizing Data Using COMPUTE and COMPUTE BY