Deals Of The Week - hours only!Up to 80% off on all courses and bundles.-Close
Total revenue for multiple periods
Calculating deltas
Creating "revenue in quarters" reports
13. Creating new columns with custom values


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:

    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:

  WHEN expression
    THEN result_true
  ELSE result_false

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'.


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:

  WHEN Amount > 1000.0
    THEN 'high' 
  ELSE 'low'
END AS OrderValue