A ROLLUP grouping is an extension to the GROUP BY clause that produces a result set containing sub-total rows in addition to the “regular” grouped rows.
Subtotal rows are “super-aggregate” rows that contain further aggregates whose values are derived by applying the same column functions that were used to obtain the grouped rows. These rows are called sub-total rows, because that is their most common use; however, any column function can be used for the aggregation.
A ROLLUP grouping is a series of grouping-sets. The general specification of a ROLLUP with “n” elements.
GROUP BY ROLLUP(C1,C2,…,Cn-1,Cn)
It is equivalent to Grouping sets:
GROUP BY GROUPING SETS((C1,C2,…,Cn-1,Cn),
SQL for ROLLUP
SELECT workdept, edlevel, sex, Sum(salary) AS SUM_SALARY, Avg(salary) AS AVG_SALARY, Count(*) AS COUNT FROM dsn81110.emp WHERE salary > 20000 GROUP BY rollup ( workdept, edlevel, sex )
WORKDEPT EDLEVEL SEX SUM_SALARY AVG_SALARY COUNT A00 14 M 29250.00 29250.00000000 1 A00 18 F 52750.00 52750.00000000 1 A00 19 M 46 500.00 46500.00000000 1 B01 18 M 41250.00 41250.00000000 1 C01 16 F 23800.00 23800.00000000 1 . . . NULL NULL NULL 750075.00 30003.00000000 1
Note that the ‘n’ elements of the ROLLUP translate to n+1 grouping sets. Note also that the order in which the grouping-expressions is specified is significant for ROLLUP.
ROLLUP and Grouping sets SQL:
GROUP BY ROLLUP(a,b)
Is equivalent to
GROUP BY GROUPING SETS((a,b), (a), () )
While GROUP BY ROLLUP(b,a)
Is the same as
GROUP BY GROUPING SETS((b,a), (b), () )
With support for ROLLUP, cube, and grouping-sets specifications, the SQL coding complexity can be reduced greatly and the SQL performance can be improved dramatically.