Very well done!
There's also an alternative way of counting objects based on custom classifications in your reports. Take a look:
WHEN ship_country = 'USA' OR ship_country = 'Canada' THEN order_id
END) AS free_shipping,
WHEN ship_country != 'USA' AND ship_country != 'Canada' THEN order_id
END) AS paid_shipping
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
ship_country. If it's
order_id 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
free_shipping column will only count orders shipped to the USA or Canada. The
paid_shipping 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.