OLTP grouping feature "grand-total" in DB2 11 or later

18 janvier 2019

If you want to show the total salary of all employees by department, you only have to code a GROUP BY over WORKDEPT clause. But in most time, you also want to display the total of all the departments.  To achieve this, you have to code a UNION (or use a CTE - not covered in this article) :

which gives the following result set :
Since DB2 11, it is possible to use the GROUPING SETS features to simplify this kind of request. You have to code a GROUPING SETS with 2 parameters : one for WORKDEPT and the second parameter “()” represents the "grand-total". :
This gives the following result :
As you can see, the "grand-total" is represented by a NULL value in WORKDEPT. 
To have exactly the same result we got using the UNION clause, here is the trick : use of a COALESCE function around the WORKDEPT !


The major advantage of this feature is to simplify the SQL coding. But in terms of GETPAGE activity, with UNION the base table was read two times, one time for each subselect.  While using GROUPING SETS, the table has only been read once. 
Alain PARY
Co-founder Dbizners
Feel free to send me your comments and ask questions.