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

Wrap course as a gift

Prepare your gift and double-check it looks just right.

Form validation errors

Recipient's Name:
Recipient's Email:
Your Message (Optional):
Proceed to Checkout

GROUP BY Extensions in MS SQL Server

Introduction
GROUPING SETS
Summary

Instruction

Nice job!

What can we do with those NULL values? Naturally, we can use COALESCE():

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

In the query above, any NULL values in the RepairCenter column will be replaced with the word ALL.

Exercise

Show the sum of all Principal amounts for two grouping sets:

  1. SalesPerson
  2. Country

Show the following columns in the query result: SalesPerson, Country, and SumPrincipal.

Instead of NULL values in the columns SalesPerson and Country, show a double dash (--).

Stuck? Here's a hint!

Start with:

SELECT
  COALESCE(SalesPerson, '--') AS SalesPerson