1. Use Case When to Derive New Columns
The CASE WHEN (or CASE) assertion permits for evaluating circumstances whose outcomes can be utilized to create new columns.
As an illustration, we will create a brand new column price_group with values low, medium, excessive primarily based on product costs. It may be thought-about as creating product worth teams.
SELECT
product_description,
worth,
CASE
WHEN worth > 20 THEN 'excessive'
WHEN worth <= 20 AND worth > 10 THEN 'medium'
WHEN worth <= 10 THEN 'low'
END AS price_group
FROM product_inventory
The CASE WHEN assertion creates the product_column primarily based on the next standards:
- If the value is larger than 20, the worth is “excessive”.
- If the value is between 10 and 20, the worth is “medium”.
- If the value is lower than 10, the worth is “low”.
We will additionally write this question as follows:
SELECT
product_description,
worth,
CASE
WHEN worth > 20 THEN 'excessive'
WHEN…
