Let's get started! The first method we're going to show allows you to put groups into separate rows.
Suppose we want to show the number of orders shipped to North America and the number of orders shipped to other places in separate rows, like this:
We can use the following query:
WITH OrdersByGroup AS (
WHEN ShipCountry IN (N'USA', N'Canada', N'Mexico')
THEN N'North America'
END AS ShippingContinent
COUNT(OrderID) AS OrderCount
GROUP BY ShippingContinent;
Inside the inner query, we select the
OrderID and use the
CASE WHEN construction to classify orders based on the
ShipCountry column. The classification result is stored in a column named
ShippingContinent, which is either
N'North America' or
N'Other'. You can define as many values as you want; you don't need to limit yourself to two.
In the outer query, we group all rows from the inner query by the
ShippingContinent column and use the
COUNT(OrderID) function to count matching orders. As a result, each group is shown in a separate row.