Level 11: Conditions in the result: CASE

Lesson

CASE works like an “if… then… else”: it computes a different value depending on a condition. Its form: CASE WHEN condition THEN value WHEN other_condition THEN other_value ELSE default_value END. The whole thing produces a single value, which you give an alias.

The conditions are tested in order, and the first true one wins: the following ones are not looked at. So the order matters. With WHEN rating >= 6.5 THEN 'good' written first, a movie rated 8.2 would get 'good' and never 'top'. Write the conditions from the strictest to the least strict.

Without ELSE, a row that meets no condition gets NULL. An explicit ELSE avoids surprises.

CASE can be used anywhere a value can go. In the SELECT, it creates a category. In a GROUP BY, it groups by category: how many full flights, how many flights not full. In an ORDER BY, it imposes a custom order.

Inside an aggregate, it allows conditional counting. SUM(CASE WHEN grade >= 10 THEN 1 ELSE 0 END) adds 1 for each grade of at least 10 and 0 for the others: you get the number of passing grades. Several SUM(CASE …) in the same SELECT count several categories on a single row.

A useful reminder for rates: dividing two whole numbers gives a whole number. seats_sold / capacity is 0 for 150 / 180; seats_sold * 1.0 / capacity gives 0.833…

Syntax

SELECT col,
       CASE
         WHEN x >= 10 THEN 'high'
         WHEN x >= 5 THEN 'medium'
         ELSE 'low'
       END AS level
FROM my_table;

Worked example

SELECT title,
       rating,
       CASE
         WHEN rating >= 7.5 THEN 'top'
         WHEN rating >= 6.5 THEN 'good'
         ELSE 'average'
       END AS verdict
FROM movies;

A movie rated 8.2 stops at the first true condition and gets 'top'.

Example result

Key points

• CASE … END creates a value based on conditions.
• The first true condition wins.
• SUM(CASE WHEN … THEN 1 ELSE 0 END) counts under a condition.

Common pitfalls

• Forgetting END causes a syntax error.
• Dividing two whole numbers gives a whole number rounded down: 150 / 180 is 0. Multiply by 1.0 to get a rate.

SQLite specifics

Grouping on an alias (GROUP BY band) works in SQLite, PostgreSQL and MySQL. SQL Server forbids it and requires repeating the whole CASE expression in the GROUP BY; Oracle only accepts it since its 23ai version. Repeating the expression remains the most portable form.

The level’s 5 exercises

1. Guided · Show the name and price of each product, with a column price_band that is 'cheap' if the price is below 20, and 'expensive' otherwise. (Table: products)
2. Practice · For each match, show its id and a column result: 'home' if the home team won, 'away' if the visiting team won, 'draw' on a tie. (Table: matches)
3. Practice · Show the id, the type and the price of the rooms: first the suites, then the doubles, then the singles; for the same type, from the most expensive to the cheapest. (Table: rooms)
4. Practice · In a single row, show the number of grades of at least 10 (passed) and the number of grades below 10 (failed). (Table: grades)
5. Challenge · Classify the flights as 'full' (at least 90% of seats sold) or 'not full', and show the number of flights in each category. (Table: flights)

In SpeedQL, every query is checked straight away: its result is compared with the expected one, then the query is run again on a hidden control database. Each exercise has written hints, to show only if you get stuck.

See also: the cheat sheet card · the SQL exercises with solutions on this topic