Very well done!
Before we introduce free shipping to the USA and Canada, we'd like to know how many orders are sent to these countries and how many are sent to other places. Take a look:
WHEN ship_country = 'USA' OR ship_country = 'Canada' THEN 0.0
END AS shipping_cost,
COUNT(*) AS order_count
GROUP BY shipping_cost;
SELECT clause, we used the
CASE WHEN construction you've seen before. However, you can see that in the
GROUP BY clause, we don't repeat this construction but just use the column alias
shipping_cost. Note that in some other databases we would need to repeat the whole
CASE WHEN construction.
Because we now group by the
CASE WHEN construction, we can add a
COUNT(*) column in the
SELECT clause. The query will show: