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
13. Creating new columns with custom values
Summary

## Instruction

There's one more report type we want to show you in this part: the revenue-in-quarters report. First, however, we need to introduce a SQL construction you might not have heard about. Take a look:

SELECT
order_id,
CASE
WHEN ship_country IN ('USA', 'Canada', 'Mexico')
THEN 'North America'
ELSE 'Elsewhere'
END AS continent
FROM orders;


We'll obtain the following result:

order_id continent
... ...
10258 Elsewhere
10259 North America
10260 Elsewhere
... ...

The construction:

CASE
WHEN expression
THEN result_true
ELSE result_false
END

checks if the expression part is true. If it is, the result_true part from the THEN clause is used. Otherwise, the result_false part from the ELSE clause is used. In this case, we check the value in the ship_country column. Based on that value, we set the value of the new column to either 'North America' or 'Elsewhere'.

## Exercise

For each order shipped to Germany, show three columns:

1. order_id
2. amount
3. order_value – set this value to 'high' if the order's amount is greater than \$1,000. Otherwise, set it to 'low'.

### Stuck? Here's a hint!

Use the following code for the last column:

CASE
WHEN amount > 1000.0
THEN 'high'
ELSE 'low'
END AS order_value