End of Summer - 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
  OrderId,
  CASE
    WHEN ShipCountry IN ('USA', 'Canada', 'Mexico')
      THEN 'North America' 
    ELSE 'Elsewhere'
  END AS Continent
FROM Orders;

We'll obtain the following result:

OrderId 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 ShipCountry 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. OrderId
  2. Amount
  3. OrderValue – 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 OrderValue