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

Good job! Just like CUBE and ROLLUP, GROUPING SETS work well with GROUPING_ID:

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

The order of the columns in GROUPING_ID is up to you, but note that different values will be generated if you change the column order.

Exercise

Find the maximal principal amount for the following grouping levels:

  1. SalesPerson
  2. Year
  3. Country

Show the following columns in the query result: GroupingId, SalesPerson, Year, Country, and MaxPrincipal. The GROUPING_ID() should have the following columns in its parentheses: SalesPerson, Year, and Country.

Stuck? Here's a hint!

This template may come in handy:

SELECT
  GROUPING_ID(A, B) AS GroupingId,
  A,
  B,
  ...
FROM X
GROUP BY GROUPING SETS
(
  (A),
  (B)
)