Deals Of The Week - 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  