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:
SELECT
CASE
WHEN ship_country = 'USA' OR ship_country = 'Canada' THEN 0.0
ELSE 10.0
END AS shipping_cost,
COUNT(*) AS order_count
FROM orders
GROUP BY
CASE
WHEN ship_country = 'USA' OR ship_country = 'Canada' THEN 0.0
ELSE 10.0
END;
In the SELECT clause, we used the CASE WHEN construction you've seen before. However, you can also see that the same CASE WHEN construction appears in the GROUP BY clause, only without the shipping_cost alias. Even though we already defined it in the SELECT clause and gave it an alias (shipping_cost), most databases don't allow referring to an alias in the GROUP BY clause (i.e., we can't write GROUP BY shipping_cost). That's why we had to repeat the whole construction. (Note that some databases, like PostgreSQL or MySQL, allow us to refer to column aliases in GROUP BY. However, this is a feature of these databases. The standard SQL doesn't allow it. It's best to know how to write the correct query in both cases.)
Because we now group by the CASE WHEN construction, we can add a COUNT(*) column in the SELECT clause. The query will show:
| shipping_cost | order_count |
|---|---|
| 10.0 | 678 |
| 0.0 | 152 |
Remember: The CASE WHEN construction should be the same in both the GROUP BY and SELECT clauses!
Create a report that shows the number of products supplied from a specific continent. Display two columns: supplier_continent and product_count. The supplier_continent column should have the following values:
'North America' for products supplied from 'USA' and 'Canada'.'Asia' for products from 'Japan' and 'Singapore'.'Other' for other countries.Use the following CASE WHEN statement in the SELECT clause:
CASE WHEN ... THEN 'North America' WHEN ... THEN 'Asia' ELSE 'Other' END AS supplier_continent
Repeat the same CASE WHEN structure in the GROUP BY clause, but remove the alias.
