Best April deals - hours only!Up to 80% off on all courses and bundles.-Close
Introduction
Total revenue for multiple periods
Calculating deltas
Creating "revenue in quarters" reports
14. Creating columns with custom sums
Summary

Instruction

Perfect! Now, let's say we want to create two columns which show different order sums based on the ShipCountry column. We can use the construction we've just learned, like so:

SELECT
  SUM(CASE
    WHEN ShipCountry IN ('USA', 'Canada', 'Mexico')
      THEN Amount
    ELSE 0.0
  END) AS SumNorthAmerica,
  SUM(CASE
    WHEN ShipCountry NOT IN ('USA', 'Canada', 'Mexico')
      THEN Amount
    ELSE 0.0
  END) AS SumElsewhere
FROM Orders;

Above, we twice used the CASE WHEN construction from the previous exercise along with the SUM() function. For each row, CASE WHEN checks the value in the ShipCountry column. The order's Amount is only added if the ShipCountry value matches the given condition. Otherwise, 0.0 is added. As a result, we'll get the total sum from orders shipped to the USA, Canada, and Mexico in the first column and the total sum from orders shipped to all other countries in the second column.

Exercise

Show two columns:

  1. SumHighFreight – The total amount generated by all orders with Freight values above 100.0.
  2. SumLowFreight – The total amount generated by all orders with Freight values equal to or less than 100.0.

Stuck? Here's a hint!

Calculate the first column as:

SUM(CASE
  WHEN Freight > 100
    THEN Amount
  ELSE 0.0
END) AS SumHighFreight