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 CustomerId and RepairCenter
2. per DateReceived 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 DateReceived, 
  CustomerId, 
  RepairCenter, 
  AVG(RepairDuration) AS AvgRepairDuration
FROM WarrantyRepair
GROUP BY CustomerId, RepairCenter
UNION ALL
SELECT
  DateReceived, 
  NULL, 
  NULL, 
  AVG(RepairDuration) AS AvgRepairDuration
FROM WarrantyRepair
GROUP BY DateReceived

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.