Popular ROLLUP SQL query to apply on your data to group it quickly

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    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.