Deals Of The Week - 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
12. CASE WHEN with SUM
Summary

Instruction

Excellent! The same kind of report can also be created using SUM() instead of COUNT(). Take a look:

SELECT 
  SUM(CASE
    WHEN ShipCountry = N'USA' OR ShipCountry = N'Canada' THEN 1
  END) AS FreeShipping,
  SUM(CASE
    WHEN ShipCountry != N'USA' AND ShipCountry != N'Canada' THEN 1
  END) AS PaidShipping
FROM Orders;

In the above query, we used SUM() with CASE WHEN instead of COUNT(). The CASE WHEN construction inside the SUM() function is very similar to that inside COUNT(), but you can see that we pass a 1 to SUM() when the condition is satisfied. This is a bit different from COUNT(), where we passed in the column name.

Despite their minor differences, both SUM() and COUNT() produce identical results in this query.

Exercise

The template presents a solution to the previous exercise. Modify it so that it uses SUM() instead of COUNT().

Stuck? Here's a hint!

You need to change the function and the CASE WHEN instruction inside it.