DB2+Mainframe+USA+Jobs
DB2+Mainframe+USA+Jobs

DB2V11,Grouping Sets-Mainframe.

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)

WOKKDEPT
EDLEVEL SEX SUM SALARY AVG SALARY COUNT
A00 NULL NULL 204250 40850 5
B01 NULL NULL 41250 41250 1
C01 NULL NULL 118890 29722.5 4
Dll NULL NULL 258350 25835 10
D21 NULL NULL 143250 28650 5
E01 NULL NULL 40175 40175 1
Ell NULL NULL 82250 27416.67 3
E21 NULL NULL 124570 24914 5
NULL 14 NULL 157570 26261.67 6
NULL 15 NULL 27380 27380 1
NULL 16 NULL 332655 27721.25 12

SQL query:

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

Advertisements