Very well done!
There's also an alternative way of counting objects based on custom classifications in your reports. Take a look:
SELECT
COUNT(CASE
WHEN ship_country = 'USA' OR ship_country = 'Canada' THEN order_id
END) AS free_shipping,
COUNT(CASE
WHEN ship_country != 'USA' AND ship_country != 'Canada' THEN order_id
END) AS paid_shipping
FROM orders;
The query will show:
| free_shipping | paid_shipping |
|---|---|
| 152 | 678 |
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 'USA' or 'Canada', the order_id is passed to COUNT() and counted. If there's a different value in ship_country, 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.
How many customers are represented by owners (contact_title = 'Owner'), and how many aren't? Show two columns with appropriate values: represented_by_owner and not_represented_by_owner.
Use the contact_title column from the customers table. If that column equals 'Owner', the customer is represented by the business owner.
