End of Summer - hours only!Up to 80% off on all courses and bundles.-Close
Introduction
Custom classifications of business objects
Custom grouping of business objects
7. Custom grouping
Custom counting of business objects
Summary

Instruction

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 shipping_cost;

In the 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:

shipping_cost order_count
10.0 678
0.0 152

Exercise

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.

Stuck? Here's a hint!

Use the following CASE WHEN statement in the SELECT clause:

CASE
  WHEN ... THEN 'North America'
  WHEN ... THEN 'Asia'
  ELSE 'Other'
END AS supplier_continent

Remember to GROUP BY supplier_continent.