Generating Derived Fields

In data science and machine learning, it is common to generate new fields using existing ones. SQL enables us to do this easily using the CASE function. If you are familiar with programming languages such as Python, C# or Java, it performs a similar function to the if...else statements.

We will demonstrate this feature using the classicmodels database, which you can download from mysqltutorial.

Order Details

The orderdetails table stores the product code, quantity of each item ordered and the price of each item.

We want to calculate the total amount of each order and if the total is more than $10,000, we will offer a discount of $500 and display the discounted total cost in a new column.

In psuedocode, the algorithm should perform the following steps.

Calculate the total amount of each order.
    
If total amount is larger than $10,000, 
    
    Subtract $500 from the total

Else

    Do nothing

We encode this logic into the CASE... WHEN ... END block in the following query.

SELECT
   orderNumber,
   SUM(quantityOrdered * priceEach) AS 'orderTotal',
   CASE
      WHEN
         SUM(quantityOrdered * priceEach) > 10000 
      THEN
         SUM(quantityOrdered * priceEach) - 500 
      ELSE
         SUM(quantityOrdered * priceEach) 
   END
   AS 'discountedAmount' 
FROM
   orderdetails 
WHERE
   orderNumber IN 
   (
      10110, 10111, 10112
   )
GROUP BY
   orderNumber 
ORDER BY
   orderTotal ASC;