The GROUPING SETS option can be thought of as the union of two or more groups of rows into a single result set. It is logically equivalent to the union of multiple subselects with the group by clause in each subselect corresponding to one grouping set. This is similar to the DB2 for Linux, UNIX, and Windows and DB2 for IBM System i® support for grouping-sets and super-group specifications.
SQL statement by using the GROUP BY clause with the GROUPING SETS option.
(See SQL query to use grouping sets)
|EDLEVEL||SEX||SUM SALARY||AVG SALARY||COUNT|
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 GROUPING SETS (WORKDEPT, EDLEVEL, SEX)
The result set is logically equivalent to the union all of three subselects with the group by clause in each subselect corresponding to one column each from the three columns on the grouping sets specification (while the other two column values are shown as NULLs).