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 46500.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 2 5
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),
GROUP BY ROLLUP(b,a)
Is the same as
GROUP BY GROUPING SETS((b,a),
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.