SQL CASE WHEN: Labeling Rows
CASE is SQL's if/else. Use it to bucket numbers into readable labels.
A raw number like quantity_on_hand is precise but not very readable in a report. CASE lets you translate values into friendly labels.
SQL checks each WHEN from top to bottom and stops at the first one that's true, so order matters. Anything left over falls through to ELSE.
select product_name,
price,
case
when price >= 30 then 'premium'
when price >= 15 then 'mid'
else 'budget'
end as price_band
from productsOrder conditions from specific to general
A CASE expression is not just a list of labels; it is a decision tree. Put the most specific condition first so it gets a chance to match before a broader rule catches the row.
In this lesson, quantity_on_hand = 0 belongs before quantity_on_hand < reorder_level. If the low-stock rule came first, an out-of-stock product would be labelled "low" instead of "out of stock."
Use CASE WHEN to label code values
CASE is also useful when a table stores short codes or business terms and the report needs readable labels.
select transaction_id,
amount,
case
when concept = 'aporte' then 'Contribution'
when concept = 'interes' then 'Interest'
when concept = 'descuento' then 'Discount'
else 'Other'
end as concept_label
from transactionsSchema · Garden ShopTable · products9 columns · 24 rows
One row per product, with price, cost, and inventory levels.
For every product, add a stock_status column:'out of stock' when nothing is on hand,'low' when the quantity is below the reorder level, and'healthy' otherwise. Show the lowest stock first.
- Columns: product_name, quantity_on_hand, reorder_level, stock_status.
- Rows: 24 products.
- Hanging Basket (0 on hand) is 'out of stock'; Pothos 'Golden' (8 < 12) is 'low'.