Deals Of The Week - hours only!Up to 80% off on all courses and bundles.-Close
Introduction
GROUPING SETS
Summary

Instruction

All right, let's get started with GROUPING SETS.

Imagine that you need to create a report on the average repair duration for two grouping levels:

  1. per customer_id and repair_center.
  2. per date_received date.

You don't want to create any additional grouping levels because you need the report to stay clear and simple.

There is no way you could use either ROLLUP or CUBE to create those grouping levels. One thing you could do is write two separate queries and join them with UNION ALL:

SELECT
  NULL AS date_received, 
  customer_id, 
  repair_center, 
  AVG(repair_duration) AS avg_repair_duration
FROM warranty_repair
GROUP BY customer_id, repair_center
UNION ALL
SELECT
  date_received, 
  NULL, 
  NULL, 
  AVG(repair_duration) AS avg_repair_duration
FROM warranty_repair
GROUP BY date_received

Note that UNION ALL requires both queries to have the same number of columns. This is why we needed to add some NULLs as columns in the SELECT clause.

Exercise

Run the template query to see how it works.