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 (
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
COUNT(CustomerID) AS CustomerCount
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.