Very well done!
There's also an alternative way of counting objects based on custom classifications in your reports. Take a look:
WHEN ShipCountry = N'USA' OR ShipCountry = N'Canada' THEN OrderID
END) AS FreeShipping,
WHEN ShipCountry != N'USA' AND ShipCountry != N'Canada' THEN OrderID
END) AS PaidShipping
The query will show:
The query above may come as a surprise because there's a
CASE WHEN construction inside the
COUNT() function. For each row, the
CASE WHEN construction checks the value in
ShipCountry. If it's
OrderID is passed to
COUNT() and counted. If there's a different value in
CASE WHEN returns a
NULL – and you already learned that a
NULL value isn't counted by
COUNT(). This way, the
FreeShipping column will only count orders shipped to the USA or Canada. The
PaidShipping column is constructed in a similar way.
You can see that the technique above involves creating a separate column for each group. The query produces a different result than the query with the
CASE WHEN in the
GROUP BY clause, which listed each group as a row, not a column.