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.

The differences between CUBE and ROLLUP are:

For example, a simple table Inventory contains:

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 these advantages:

See Also
SELECT Summarizing Data Using COMPUTE and COMPUTE BY

  


(c) 1988-98 Microsoft Corporation. All Rights Reserved.