Suppose we want to show the availability level for each product in a report, but we don't want to simply print the units_in_stock
column. The column contains a precise number of units available, but we only need an overall level, like 'low'
or 'high'
. How can we do this? Take a look:
SELECT
product_id,
product_name,
units_in_stock,
CASE
WHEN units_in_stock > 100 THEN 'high'
WHEN units_in_stock > 50 THEN 'moderate'
WHEN units_in_stock > 0 THEN 'low'
WHEN units_in_stock = 0 THEN 'none'
END AS availability
FROM products;
We create a brand-new column named availability
. You can see that we use a special construction called CASE WHEN
. It returns different values based on the conditions you specify.
How do we build a CASE WHEN
? We start with the word CASE
, followed by one or more WHEN ... THEN ...
conditions. After the list of conditions, we add the END
keyword to indicate the end of the construction. CASE WHEN
looks for the first condition satisfied in the WHEN
part and takes its value from the THEN
part.
In the query above, CASE WHEN
checks the value of the units_in_stock
column. If it's greater than 100
, the availability
column gets the value 'high'
. If it's not greater than 100
, but is greater than 50
, the column value is 'moderate'
. And when it's less than 50
, its value is 'low'
. Zero units in stock, of course, gets a value of 'none'
.