THE GROUPING SETS option’s real usage is to make the union of two or more groups of rows into a single result set. Introduced in DB2V11. The best example given and explained to use it and improve your SQL Query.
Grouping Sets in DB2
- 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 Bottom Line
The result set is logically equivalent to the union of 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).
Srinimf 