Databases Reference
In-Depth Information
Rollups generates a subtotal line for every set in the
Rollup
clause. The sets in this
case might not be as straightforward as with the grouping sets (they were between
parenthesis). With the
Rollup
clause, the grouping sets are a combination of the
arguments, but not in a random order. Let's take a look at an example to identify
the sets.
SQL> select deptno
2 , sum (sal)
3 from emp
4 group by rollup
5 (deptno)
6 /
DEPTNO SUM(SAL)
---------- ----------
10 8750
20 10875
30 9400
29025
There are two grouping sets in the above example, one for
deptno
and another for
the empty set. The
Rollup
expression in the preceding example can be written
as follows:
group by grouping sets
((),deptno)
The method to determine the grouping sets with the
Rollup
clause is as follows:
• First all the arguments in the
Rollup
clause are one grouping set
• Then all the arguments minus the last one comprise the next grouping set
• All the arguments minus the last two comprise the following grouping set,
and so on.
Until there are no more arguments left, the empty set is added as the inal grouping
set—the empty set being the grand total. Rollup is really a shorthand notation for
combining multiple grouping sets to create multiple subtotals as well as the grand
total. Rollups come in very handy for management reports where this information is
often required.
