ROLLUP or Sub Total in DB2 V11 SQL

DB2+Developer+JOBS
DB2+Developer+JOBS

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.

Syntax:

GROUP BY ROLLUP(C1,C2,…,Cn-1,Cn)

It is equivalent to Grouping sets:

GROUP BY GROUPING SETS((C1,C2,…,Cn-1,Cn),
(C1,C2,…,Cn-1),

(C1,C2),
(C1),
() )

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)

Result:

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),
(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.

Advertisements

Author: Srini

Experienced software developer. Skills in Development, Coding, Testing and Debugging. Good Data analytic skills (Data Warehousing and BI). Also skills in Mainframe.