SQL CASE WHEN: write conditions in a query

“If the price is above €100, show premium, otherwise standard.” For this kind of logic, SQL offers the CASE WHEN expression, the equivalent of “if… then… else” in programming languages. You can use it anywhere a value is expected: in the SELECT, the ORDER BY, and even inside a SUM.

Syntax

CASE
  WHEN condition1 THEN value1
  WHEN condition2 THEN value2
  ELSE default_value
END

How it works

SQL tests the WHEN conditions in order and returns the value of the first true condition. If none is true, it returns the ELSE value, or NULL if there is no ELSE. The word END closes the expression; it is often given an alias with AS.

A short form

To compare a single column with several values: CASE statut WHEN 'livrée' THEN 'OK' WHEN 'annulée' THEN 'KO' ELSE '…' END.

Counting based on a condition

SUM(CASE WHEN condition THEN 1 ELSE 0 END) counts the rows that meet a condition. With several expressions of this kind, you get a cross-tab in a single query.

Diagram

Simplified diagram to show the idea. The examples below use the real SQL Quest practice database (a fictional online shop: see the full schema).

Examples with their results

SELECT nom, prix,
  CASE
    WHEN prix >= 100 THEN 'premium'
    WHEN prix >= 30 THEN 'mid-range'
    ELSE 'budget'
  END AS price_range
FROM produits
ORDER BY prix DESC
LIMIT 8;

SELECT
  CASE
    WHEN age IS NULL THEN 'unknown'
    WHEN age < 30 THEN 'under 30'
    WHEN age < 50 THEN '30 to 49'
    ELSE '50 and over'
  END AS age_group,
  COUNT(*) AS nb
FROM clients
GROUP BY age_group
ORDER BY nb DESC;

SELECT strftime('%Y', date_commande) AS annee,
       COUNT(*) AS total,
       SUM(CASE WHEN statut = 'annulée' THEN 1 ELSE 0 END) AS annulees
FROM commandes
GROUP BY annee;

Common mistakes

SELECT nom, CASE WHEN prix > 100 THEN 'expensive' ELSE 'affordable' AS price_range FROM produits;

SELECT nom, CASE WHEN prix > 100 THEN 'expensive' ELSE 'affordable' END AS price_range FROM produits;

SELECT nom, CASE WHEN prix >= 30 THEN 'mid-range' WHEN prix >= 100 THEN 'premium' END FROM produits;

SELECT nom, CASE WHEN prix >= 100 THEN 'premium' WHEN prix >= 30 THEN 'mid-range' END FROM produits;

SELECT SUM(CASE WHEN statut = 'livrée' THEN 1 END) FROM commandes;

SELECT SUM(CASE WHEN statut = 'livrée' THEN 1 ELSE 0 END) FROM commandes;

Exercises

Try writing the query yourself before opening the solution.

SELECT nom, stock, CASE WHEN stock = 0 THEN 'out of stock' WHEN stock < 20 THEN 'low' ELSE 'ok' END AS availability FROM produits;

SELECT ville, SUM(CASE WHEN age < 30 THEN 1 ELSE 0 END) AS under_30, SUM(CASE WHEN age >= 30 THEN 1 ELSE 0 END) AS others FROM clients GROUP BY ville;

Frequently asked questions