CASE WHEN — Conditional Expressions

~10 min · sql, case, conditional

Level 0Scout

0 XP0/80 lessons0/10 achievements

0/120 XP to next level120 XP to go0% complete

SQL's if/elif/else

CASE WHEN is SQLite's only conditional expression in SELECT lists. Two shapes:

Simple CASE: CASE col WHEN v1 THEN r1 WHEN v2 THEN r2 ELSE r3 END — equality on a single column.
Searched CASE: CASE WHEN cond1 THEN r1 WHEN cond2 THEN r2 ELSE r3 END — arbitrary boolean conditions. More flexible, what you'll use 90% of the time.

Tip: CASE inside aggregates is incredibly powerful — sum(CASE WHEN status='paid' THEN total END) gives you a conditional sum without subqueries. This pattern shows up in almost every reporting query you'll ever write.

Code

CASE for derived columns·sql

SELECT id, role,
       CASE
         WHEN role = 'admin'      THEN 'priority'
         WHEN role = 'moderator'   THEN 'support'
         ELSE 'standard'
       END AS tier
FROM users;

CASE inside aggregates — pivot in one query·sql

SELECT brain,
       sum(CASE WHEN role = 'user'      THEN 1 END) AS user_msgs,
       sum(CASE WHEN role = 'assistant' THEN 1 END) AS assistant_msgs
FROM   messages
GROUP  BY brain;

External links

CASE Expression

Exercise

On the messages table, write a single query that returns one row per brain with three columns: total messages, user messages, assistant messages — using CASE WHEN inside SUM. Then add a fourth column that classifies the brain as 'busy' / 'normal' / 'quiet' based on total counts.

Progress

Progress is local-only — sign in to sync across devices.

← PreviousSubqueries — Inline Selects Next →String Functions That Earn Their Keep

Spotted a bug or have feedback on this page?Report an Issue

Comments 0

🔔 Reply notifications (sign in)

No comments yet — be the first.