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

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 !

 

 

 

Summary

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.