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
