Rainbow Deals - hours only!Up to 80% off on all courses and bundles.-Close
Introduction
Basic multi-level aggregation
Multi-level aggregation in groups
Multi-level aggregation with custom classification
11. Multi-level aggregation with CASE WHEN – theory
Three or more aggregation levels
Summary

Instruction

Perfect! We can also combine multi-level aggregation with custom classifications. Suppose we want to find the number of customers divided into three groups: those with less than 10 orders, those with 10–20 orders, and those with more than 20 orders. Here's a query that can do just that:

WITH CustomerOrderCounts AS (
  SELECT
    CustomerID, 
    CASE
      WHEN COUNT(O.OrderID) > 20
        THEN N'more than 20' 
      WHEN COUNT(O.OrderID) <= 20 AND COUNT(O.OrderID) >= 10
        THEN N'between 10 and 20'
      ELSE N'less than 10'
    END AS OrderCountCat
  FROM Orders O
  GROUP BY CustomerID
) 

SELECT
  OrderCountCat,
  COUNT(CustomerID) AS CustomerCount
FROM CustomerOrderCounts
GROUP BY OrderCountCat;

In the inner query, we used the CASE WHEN construction to classify each customer into one of three groups. The customer’s group is shown in the OrderCountCat column. Next, we used the OrderCountCat column in the outer query with the COUNT() function to show the number of customers in each group.

Exercise

Count the number of high-value and low-value customers. If the total price paid by a given customer for all their orders is more than $20,000 before discounts, treat the customer as N'high-value'. Otherwise, treat them as N'low-value'.

Create a report with two columns: Category (either N'high-value' or N'low-value') and CustomerCount.

Stuck? Here's a hint!

Use the following CASE WHEN construction in the inner query:

CASE
  WHEN SUM(Quantity * UnitPrice) > 20000
    THEN N'high-value' 
  ELSE N'low-value'
END AS Category