Introduction
GROUPING SETS
Summary

Instruction

Good job! As you saw, using UNION ALL is one way of dealing with such reports, but it seems to cause all kinds of problems:

  1. The statement becomes huge as more queries are added with UNION ALL
  2. The table has to be accessed multiple times, which affects performance
  3. Adding NULL values in the SELECT clause is awkward and error prone

That's where GROUPING SETS come in handy. They allow you to perform multiple groupings within a single query; each grouping is explicitly stated, as we see below:

SELECT
  DateReceived, 
  CustomerId,   
  RepairCenter, 
  AVG(RepairDuration) AS AvgRepairDuration
FROM WarrantyRepair
GROUP BY GROUPING SETS
(
  (CustomerId, RepairCenter),
  (DateReceived)
)

As you can see, GROUP BY GROUPING SETS is followed by a pair of parentheses. Inside, we put all the grouping combinations we wish to get, each in a separate pair of parentheses and separated by a comma.

Exercise

Run the template query. Note how two different grouping levels are created.