RSS Feed

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:

EmpIdYrSales
1200512000.00
1200618000.00
1200725000.00
2200515000.00
220066000.00
3200620000.00
3200724000.00


A simple Group by results in:

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

YrSales
200527000.00
200644000.00
200749000.00

A simple group by with ROLLUP results in:

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

YrSales
200527000.00
200644000.00
200749000.00
NULL120000.00

Another example of group by with ROLLUP:

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

EmpIdYrSales
1200512000.00
1200618000.00
1200725000.00
1NULL55000.00
2200515000.00
220066000.00
2NULL21000.00
3200620000.00
3200724000.00
3NULL44000.00
NULLNULL120000.00

[Source]


Example 2 of ROLLUP

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

ItemColorQtySum
ChairBlue101.00
ChairRed210.00
ChairALL311.00
TableBlue124.00
TableRed223.00
TableALL347.00
ALLALL658.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:

ALLBlue225.00
ALLRed433.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]

No comments:

Post a Comment