SQL Tutorial · Aggregation and Functions · Lesson 18 of 36

SQL CASE

Conditional logic inside queries with CASE WHEN.

CASE expressions

CASE returns a value based on conditions — SQL's if/else:

Example 1Try it yourself

SELECT name, price,
       CASE
         WHEN price < 20 THEN 'budget'
         WHEN price < 100 THEN 'standard'
         ELSE 'premium'
       END AS tier,
       CASE WHEN stock = 0 THEN 'sold out' ELSE 'available' END AS availability
FROM products ORDER BY price;

Output

name | price | tier | availability
Rubber Duck | 5.5 | budget | available
Coffee Mug | 12 | budget | available
Wireless Mouse | 24.99 | standard | available
SQL for Beginners | 29.99 | standard | available
Clean Code | 37.5 | standard | available
USB-C Hub | 39.95 | standard | available
The Pragmatic Programmer | 42 | standard | sold out
Desk Lamp | 45 | standard | available
Standing Desk Mat | 59.9 | standard | sold out
Mechanical Keyboard | 89 | standard | available
Robot Kit | 120 | premium | available
4K Monitor | 329 | premium | available

CASE inside aggregates

Combining CASE with SUM or COUNT builds pivot-style reports in one query:

Example 2Try it yourself

SELECT COUNT(*) AS total,
       SUM(CASE WHEN status = 'delivered' THEN 1 ELSE 0 END) AS delivered,
       SUM(CASE WHEN status IN ('pending', 'shipped') THEN 1 ELSE 0 END) AS open,
       SUM(CASE WHEN status = 'cancelled' THEN 1 ELSE 0 END) AS cancelled
FROM orders;

Output

total | delivered | open | cancelled
12 | 7 | 4 | 1

Test yourself

Which keyword ends a CASE expression?

ESAC
END
END CASE only
STOP

Show answer

Answer: END