ROLLUP

SQL ROLLUP

You can use WITH ROLLUP to generate a summary row for each group.

Example 1 of ROLLUP

Suppose we have the following table - Sales:

EmpId	Yr	Sales
1	2005	12000.00
1	2006	18000.00
1	2007	25000.00
2	2005	15000.00
2	2006	6000.00
3	2006	20000.00
3	2007	24000.00

A simple Group by results in:

SELECT Yr, SUM(Sales) AS Sales
FROM Sales
GROUP BY Yr

Yr	Sales
2005	27000.00
2006	44000.00
2007	49000.00

A simple group by with ROLLUP results in:

SELECT Yr, SUM(Sales) AS Sales
FROM Sales
GROUP BY Yr WITH ROLLUP

Yr	Sales
2005	27000.00
2006	44000.00
2007	49000.00
NULL	120000.00

Another example of group by with ROLLUP:

SELECT EmpId, Yr, SUM(Sales) AS Sales
FROM Sales
GROUP BY EmpId, Yr WITH ROLLUP

EmpId	Yr	Sales
1	2005	12000.00
1	2006	18000.00
1	2007	25000.00
1	NULL	55000.00
2	2005	15000.00
2	2006	6000.00
2	NULL	21000.00
3	2006	20000.00
3	2007	24000.00
3	NULL	44000.00
NULL	NULL	120000.00

[Source]


Example 2 of ROLLUP

Item	Color	Quantity
Table	Blue	124
Table	Red	223
Chair	Blue	101
Chair	Red	210

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

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.

[Source]