CodeRang
SQL topics

SQL CASE WHEN: Conditional Logic in Queries

SQL CASE WHEN adds conditional logic directly to a query and returns different values for different conditions. This example shows multiple WHEN branches, ELSE, branch order, and CASE inside an aggregate.

Updated · SQL Arena team

SQL from scratch courseAll problems

What you will practice

  • Build categories with CASE WHEN
  • Use CASE inside SUM and other aggregates
  • Order overlapping conditions correctly
  • Handle ELSE and possible NULL results
  • Apply CASE to flags and custom sorting

A practice problem in the trainer

This is how a problem looks in SQL Arena: the task, the editor, the result and the database schema.

PrevNextRandom
TaskSubmissionsAI Mentor
Classify customers by paid order value

For each customer, total only orders whose status is paid. Return customer_name, paid_total, and spend_group: high for totals of at least 500, medium for totals from 200 to 499.99, and low otherwise. Sort by paid_total descending, then by name.

Query 1PostgreSQLv16
SELECT c.customer_name,
       SUM(CASE WHEN o.status = 'paid' THEN o.amount ELSE 0 END) AS paid_total,
       CASE WHEN SUM(CASE WHEN o.status = 'paid' THEN o.amount ELSE 0 END) >= 500 THEN 'high'
            WHEN SUM(CASE WHEN o.status = 'paid' THEN o.amount ELSE 0 END) >= 200 THEN 'medium'
            ELSE 'low' END AS spend_group
FROM customers c JOIN orders o ON o.customer_id = c.id
GROUP BY c.id, c.customer_name
ORDER BY paid_total DESC, c.customer_name;
Submit
Resultcustomersorders
Answer accepted

How SQL CASE WHEN works

The first CASE sits inside SUM. Each order is inspected before the aggregation happens. A paid order contributes its amount, while refunded, pending, or cancelled orders contribute zero. This pattern is known as conditional aggregation and is useful whenever one grouped result needs a metric based on only part of the underlying rows.

The second CASE converts the final paid total into a label. The 500 threshold is checked before the 200 threshold because CASE stops at the first true WHEN. Reversing those branches would classify every value above 500 as medium: it already satisfies the broader condition of being at least 200, so SQL would never reach the high branch.

ELSE covers every result that matches neither threshold. Eva therefore receives low even though she has an order, because the order is cancelled and contributes zero to the conditional sum. ORDER BY makes the output deterministic. CASE can also appear inside ORDER BY when labels need a custom priority rather than alphabetical ordering.

CASE evaluates WHEN conditions from top to bottom and returns the result from the first matching branch. That makes branch order important when conditions overlap: a more specific threshold usually needs to appear before a broader one. ELSE provides a fallback result; without ELSE, CASE returns NULL when no condition matches. CASE can be used in SELECT expressions, aggregates, sorting logic, and other places where SQL accepts an expression. A common conditional aggregation pattern is SUM(CASE WHEN condition THEN amount ELSE 0 END).

SQLPostgreSQL v16
SELECT c.customer_name,
       SUM(CASE WHEN o.status = 'paid' THEN o.amount ELSE 0 END) AS paid_total,
       CASE WHEN SUM(CASE WHEN o.status = 'paid' THEN o.amount ELSE 0 END) >= 500 THEN 'high'
            WHEN SUM(CASE WHEN o.status = 'paid' THEN o.amount ELSE 0 END) >= 200 THEN 'medium'
            ELSE 'low' END AS spend_group
FROM customers c JOIN orders o ON o.customer_id = c.id
GROUP BY c.id, c.customer_name
ORDER BY paid_total DESC, c.customer_name;
Query result
customer_namepaid_totalspend_group
David700.00high
Anna540.00high
Boris240.00medium
Clara90.00low
Eva0low

Common SQL CASE WHEN mistakes

01

Putting the broad condition first

WHEN total >= 200 placed before WHEN total >= 500 captures both ranges. The second branch becomes unreachable. For overlapping numeric conditions, check the narrow or higher threshold first.

02

Forgetting what happens without ELSE

A CASE expression with no matching WHEN and no ELSE returns NULL. SUM may ignore that NULL, which can hide the issue in one query, but later arithmetic or comparisons may behave differently than expected.

03

Using CASE when rows must be removed

CASE chooses a value; it does not filter the result set. If rows must disappear completely, use WHERE or an appropriate join condition. Conditional aggregation keeps the rows and changes only their contribution to the metric.

Practice problems on this topic

SQL from scratch course

The next exercises vary the role of CASE instead of repeating the same threshold pattern. Free tasks include turning cabin Wi-Fi availability into a useful value and flipping encoded employee gender values. A more involved Kotomarket exercise builds price tiers and another creates status labels for selected orders. SQL Arena contains 1,192 checked exercises for PostgreSQL, MySQL, ClickHouse, and BigQuery. Every task has its own tables, and answers are checked against hidden data rather than a fixed visible output, so a query must express the general rule. The free plan requires no card and includes 292 exercises across all four dialects. If CASE still feels difficult because SELECT, filtering, and aggregation are not yet automatic, the SQL from Scratch course is the natural next step. Each lesson combines theory with a task, and the opening chapters are free.

Reference on this topic

In-depth articles with syntax and examples in the SQL Arena reference.

Frequently asked questions

What is SQL CASE WHEN?

CASE WHEN is a conditional SQL expression. It evaluates conditions in order and returns the value associated with the first condition that evaluates to true.

How does CASE WHEN work in SQL?

SQL checks each WHEN branch from top to bottom. Once a condition matches, its THEN result is returned and later branches are not evaluated for that CASE result.

Can SQL CASE have multiple WHEN conditions?

Yes. A CASE expression can contain multiple WHEN branches, which makes it useful for categorizing values into ranges or groups.

Is ELSE required in SQL CASE WHEN?

No. If ELSE is omitted and none of the WHEN conditions match, the CASE expression returns NULL.

Can I use CASE WHEN inside SUM?

Yes. SUM(CASE WHEN condition THEN value ELSE 0 END) is a common form of conditional aggregation used to sum only rows that meet a condition.

See also

Practice SQL CASE WHEN queries

Solve CASE exercises directly in SQL Arena and get automatic checks against hidden data. ER diagrams are available for schemas, EXPLAIN is built in, and the AI mentor can provide hints or explain errors when you get stuck.

Practice SQL CASE WHEN

More practice from CodeRang