Grouping Sets real concept to simplify SQL Query

GROUPING SETS option real usage is to make union of two or more groups of rows into a single result set. Introduced in DB2V11. 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 EDLEVELSEXSUM SALARYAVG SALARYCOUNT
A00NULLNULL204250408505
B01NULLNULL41250412501
C01NULLNULL11889029722.54
DllNULLNULL2583502583510
D21NULLNULL143250286505
E01NULLNULL40175401751
EllNULLNULL8225027416.673
E21NULLNULL124570249145
NULL14NULL15757026261.676
NULL15NULL27380273801
NULL16NULL33265527721.2512

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

Author: Srini

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