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 ShipCountry = N'USA' OR ShipCountry = N'Canada' THEN OrderID 
  END) AS FreeShipping,
  COUNT(CASE
    WHEN ShipCountry != N'USA' AND ShipCountry != N'Canada' THEN OrderID
  END) AS PaidShipping
FROM Orders;

The query will show:

FreeShipping PaidShipping
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 ShipCountry. If it's N'USA' or N'Canada', the OrderID is passed to COUNT() and counted. If there's a different value in ShipCountry, CASE WHEN returns a NULL – and you already learned that a NULL value isn't counted by COUNT(). This way, the FreeShipping column will only count orders shipped to the USA or Canada. The PaidShipping 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 (ContactTitle = N'Owner'), and how many aren't? Show two columns with appropriate values: RepresentedByOwner and NotRepresentedByOwner.

Stuck? Here's a hint!

Use the ContactTitle column from the Customers table. If that column equals N'Owner', the customer is represented by the business owner.