Oracle PL/SQL: SQL 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