Summer Deals - hours only!Up to 80% off on all courses and bundles.-Close
Introduction
Custom classifications of business objects
Custom grouping of business objects
Custom counting of business objects
9. CASE WHEN with COUNT
Summary

Instruction

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.

Exercise

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.

Stuck? Here's a hint!

Use the contact_title column from the customers table. If that column equals 'Owner', the customer is represented by the business owner.