SQL CASE WHEN: Syntax, Multiple Conditions and Examples

A CASE expression works like a teacher grading papers: 90 or more is an A, 75 or more a B, 60 or more a C, and anything else a fail. The teacher checks the rules from the top and stops at the first one that fits. CASE WHEN does exactly that for every row:

WITH students(name, marks) AS (
  VALUES ('Ishaan', 94), ('Divya', 81), ('Karan', 67), ('Sneha', 45), ('Arjun', 60)
)
SELECT name, marks,
       CASE
         WHEN marks >= 90 THEN 'A'
         WHEN marks >= 75 THEN 'B'
         WHEN marks >= 60 THEN 'C'
         ELSE 'Fail'
       END AS grade
FROM students;

▶ Run this query in the SQL Playground

Ishaan's 94 matches the first condition, so the later ones are never checked. Arjun's 60 fails the first two and matches marks >= 60. Sneha's 45 matches nothing, so she gets the ELSE value. The five students are written into the query with WITH ... VALUES, so you can change their marks in the Playground and see the grades move.

CASE
  WHEN condition_1 THEN result_1
  WHEN condition_2 THEN result_2
  ...
  ELSE default_result
END

There are no commas between the WHEN branches. The whole expression is one value, so give it a name with AS, exactly as you would any calculated column. CASE is part of standard SQL and is written the same way in MySQL, PostgreSQL, SQL Server, Oracle and SQLite.

Most real CASE expressions have several WHEN branches. Here every employee gets a salary band:

SELECT first_name, last_name, salary,
       CASE
         WHEN salary >= 150000 THEN 'High'
         WHEN salary >= 90000  THEN 'Medium'
         ELSE 'Low'
       END AS salary_band
FROM employees
ORDER BY id
LIMIT 6;

▶ Run this query in the SQL Playground

Order matters. The conditions overlap: a salary of 160,000 is both >= 150000 and >= 90000. Because CASE stops at the first true condition, the highest threshold has to come first. Swap the two branches and every high earner is labelled "Medium", as the mistakes section shows.

Combining tests with AND and OR

One WHEN can test several columns at once. This marks employees who are both in Engineering and hired before 2018:

SELECT first_name, department, hire_date,
       CASE
         WHEN department = 'Engineering' AND hire_date < '2018-01-01' THEN 'Senior engineer'
         WHEN department = 'Engineering' THEN 'Engineer'
         WHEN department IN ('Sales', 'Marketing') THEN 'Commercial'
         ELSE 'Other'
       END AS employee_group
FROM employees
WHERE department IN ('Engineering', 'Sales', 'Marketing')
ORDER BY department, hire_date
LIMIT 8;

▶ Run this query in the SQL Playground

Everything so far has been a searched CASE: each WHEN has its own condition. When you only compare one column with fixed values, the simple CASE form is shorter. The column comes once, after CASE, and each WHEN lists a value:

SELECT status,
       CASE status
         WHEN 'pending'    THEN 'Waiting'
         WHEN 'processing' THEN 'Waiting'
         WHEN 'shipped'    THEN 'On the way'
         WHEN 'delivered'  THEN 'Complete'
         ELSE 'Closed'
       END AS customer_label,
       COUNT(*) AS orders
FROM orders
GROUP BY status
ORDER BY orders DESC;

▶ Run this query in the SQL Playground

THEN and ELSE can return the result of a calculation, not just a label. That covers bonuses, tax slabs, commission rates and shipping fees: a different formula depending on a threshold. This gives a bonus that grows with length of service:

SELECT first_name, hire_date, salary,
       CASE
         WHEN hire_date < '2018-01-01' THEN ROUND(salary * 0.10)
         WHEN hire_date < '2022-01-01' THEN ROUND(salary * 0.05)
         ELSE ROUND(salary * 0.02)
       END AS bonus
FROM employees
ORDER BY id
LIMIT 6;

▶ Run this query in the SQL Playground

The same pattern gives tiered shipping fees on orders:

SELECT id, total,
       CASE
         WHEN total >= 1000 THEN 0
         WHEN total >= 250  THEN 9.99
         ELSE 14.99
       END AS shipping_fee
FROM orders
ORDER BY id
LIMIT 6;

▶ Run this query in the SQL Playground

Keep every branch the same type: all numbers here. Mixing text in one branch and a number in another is an error in PostgreSQL and SQL Server and gives surprising results elsewhere.

CASE can appear anywhere a value is allowed. In ORDER BY it gives a custom sort order. Plain ASC or DESC would sort statuses alphabetically; this sorts them in the order an order moves through:

SELECT id, status, order_date
FROM orders
ORDER BY CASE status
           WHEN 'pending'    THEN 1
           WHEN 'processing' THEN 2
           WHEN 'shipped'    THEN 3
           WHEN 'delivered'  THEN 4
           ELSE 5
         END,
         order_date DESC
LIMIT 8;

▶ Run this query in the SQL Playground

In WHERE, CASE lets the threshold depend on the row. Here Sales employees qualify above 150,000 and everyone else above 190,000:

SELECT first_name, department, salary
FROM employees
WHERE salary > CASE WHEN department = 'Sales' THEN 150000 ELSE 190000 END
ORDER BY salary DESC;

▶ Run this query in the SQL Playground

This works, but the same filter written as plain conditions is easier to read and lets the database use an index on salary or department: WHERE (department = 'Sales' AND salary > 150000) OR salary > 190000. Reach for CASE in WHERE only when the plain form becomes hard to follow.

Grouping by a CASE expression creates your own categories on the fly, without a lookup table. Count the employees in each salary band and show the average salary:

SELECT CASE
         WHEN salary >= 150000 THEN 'High'
         WHEN salary >= 90000  THEN 'Medium'
         ELSE 'Low'
       END AS salary_band,
       COUNT(*) AS employees,
       ROUND(AVG(salary)) AS avg_salary
FROM employees
GROUP BY salary_band
ORDER BY avg_salary DESC;

▶ Run this query in the SQL Playground

The 40 employees in the High band average 180517. SQLite, MySQL and PostgreSQL allow GROUP BY salary_band, the alias from SELECT. SQL Server and Oracle do not, so there you repeat the whole CASE expression in GROUP BY.

This is the most useful CASE pattern in reporting. Put a CASE inside an aggregate and the aggregate only "sees" the rows you choose. One pass over the table can then produce several totals side by side. Here is every year's order count, broken down by outcome:

SELECT strftime('%Y', order_date) AS year,
       COUNT(*) AS all_orders,
       SUM(CASE WHEN status = 'delivered' THEN 1 ELSE 0 END) AS delivered,
       SUM(CASE WHEN status IN ('cancelled', 'refunded') THEN 1 ELSE 0 END) AS cancelled_or_refunded,
       ROUND(SUM(CASE WHEN status = 'delivered' THEN total ELSE 0 END), 2) AS delivered_revenue
FROM orders
GROUP BY year
ORDER BY year;

▶ Run this query in the SQL Playground

SUM(CASE WHEN ... THEN 1 ELSE 0 END) counts the matching rows; SUM(CASE WHEN ... THEN total ELSE 0 END) adds up their totals. Without conditional aggregation this would take one query per column, or a GROUP BY that returns one row per year and status instead of one row per year.

COUNT with a CASE and no ELSE

COUNT ignores NULLs, and a CASE without ELSE returns NULL when nothing matches. So COUNT(CASE WHEN ... THEN 1 END) counts only the matching rows:

SELECT department,
       COUNT(*) AS employees,
       COUNT(CASE WHEN salary >= 150000 THEN 1 END) AS high_earners,
       COUNT(CASE WHEN status = 'on_leave' THEN 1 END) AS on_leave
FROM employees
GROUP BY department
ORDER BY high_earners DESC, department;

▶ Run this query in the SQL Playground

Conditional aggregates also work in HAVING. To keep only the departments with at least five high earners, add HAVING COUNT(CASE WHEN salary >= 150000 THEN 1 END) >= 5.

Dates are a common thing to classify: weekday or weekend, quarter, how recent. In SQLite, strftime('%w', date) returns the day of the week as text from 0 (Sunday) to 6 (Saturday):

SELECT CASE WHEN strftime('%w', order_date) IN ('0', '6') THEN 'Weekend'
            ELSE 'Weekday'
       END AS day_type,
       COUNT(*) AS orders,
       ROUND(AVG(total), 2) AS avg_order_value
FROM orders
GROUP BY day_type;

▶ Run this query in the SQL Playground

A company whose financial year starts in April needs its own quarter labels. CASE maps calendar months to fiscal quarters:

SELECT CASE
         WHEN CAST(strftime('%m', order_date) AS INTEGER) BETWEEN 4 AND 6  THEN 'Q1 (Apr-Jun)'
         WHEN CAST(strftime('%m', order_date) AS INTEGER) BETWEEN 7 AND 9  THEN 'Q2 (Jul-Sep)'
         WHEN CAST(strftime('%m', order_date) AS INTEGER) BETWEEN 10 AND 12 THEN 'Q3 (Oct-Dec)'
         ELSE 'Q4 (Jan-Mar)'
       END AS fiscal_quarter,
       COUNT(*) AS orders
FROM orders
GROUP BY fiscal_quarter
ORDER BY fiscal_quarter;

▶ Run this query in the SQL Playground

Other databases extract the parts differently (EXTRACT(DOW FROM order_date) in PostgreSQL, DAYOFWEEK() in MySQL, DATEPART(weekday, ...) in SQL Server), but the CASE around them is the same. See SQL date functions.

To test for a missing value inside CASE, use IS NULL. An order with no shipped_date has not been shipped:

SELECT CASE WHEN shipped_date IS NULL THEN 'Not shipped'
            ELSE 'Shipped'
       END AS shipping_state,
       COUNT(*) AS orders
FROM orders
GROUP BY shipping_state;

▶ Run this query in the SQL Playground

Two rules to remember. First, WHEN shipped_date = NULL is never true, so every row would fall through to ELSE. Second, a CASE with no ELSE returns NULL for rows that match nothing; that is often how blank categories appear in a report. When you only need "this value, or a default if it is NULL", COALESCE is shorter than a CASE.

Conditions can use LIKE and string functions. This segments users by email provider:

SELECT CASE
         WHEN email LIKE '%@gmail.com'   THEN 'Gmail'
         WHEN email LIKE '%@yahoo.com'   THEN 'Yahoo'
         WHEN email LIKE '%@hotmail.com' OR email LIKE '%@outlook.com' THEN 'Microsoft'
         ELSE 'Other'
       END AS provider,
       COUNT(*) AS users
FROM users
GROUP BY provider
ORDER BY users DESC;

▶ Run this query in the SQL Playground

A condition can compare with the result of a subquery. This labels each employee against the company-wide average salary:

SELECT first_name, department, salary,
       CASE WHEN salary > (SELECT AVG(salary) FROM employees) THEN 'Above average'
            ELSE 'At or below average'
       END AS compared_with_company
FROM employees
ORDER BY id
LIMIT 6;

▶ Run this query in the SQL Playground

Make the subquery correlated and the comparison is with each employee's own department instead: (SELECT AVG(salary) FROM employees e2 WHERE e2.department = e.department). The subquery must return a single value; one that can return several rows makes the query fail.

A THEN or ELSE can contain another CASE. That is legal, but it gets hard to read quickly. Both queries below label products the same way; the second is flat:

-- Nested
CASE WHEN status = 'discontinued' THEN 'Discontinued'
     ELSE CASE WHEN stock < 50 THEN 'Reorder now'
               ELSE 'In stock'
          END
END

SELECT name, status, stock,
       CASE
         WHEN status = 'discontinued' THEN 'Discontinued'
         WHEN stock < 50             THEN 'Reorder now'
         ELSE 'In stock'
       END AS stock_label
FROM products
WHERE status = 'discontinued' OR stock < 50
ORDER BY stock_label, stock;

▶ Run this query in the SQL Playground

Because CASE stops at the first true condition, a flat list with the most specific rule first does the same job as nesting. Keep nesting for cases where the inner decision really depends on the outer one, and no deeper than two levels.

IIF and FILTER are shorter ways to write the same thing. In the Playground both work:

SELECT COUNT(*) FILTER (WHERE status = 'delivered') AS delivered_filter,
       SUM(CASE WHEN status = 'delivered' THEN 1 ELSE 0 END) AS delivered_case,
       SUM(IIF(status = 'delivered', 1, 0)) AS delivered_iif
FROM orders;

▶ Run this query in the SQL Playground

All three return 82. CASE is the one that runs everywhere, so it is the safe choice for queries that may move between databases.

Six errors, most of which give wrong answers without an error message.

SELECT first_name,
       CASE WHEN salary >= 150000 THEN 'High'
FROM employees;

SELECT first_name,
       CASE WHEN salary >= 150000 THEN 'High' ELSE 'Other' END AS band
FROM employees;

CASE
  WHEN salary >= 90000  THEN 'Medium'
  WHEN salary >= 150000 THEN 'High'
  ELSE 'Low'
END

CASE
  WHEN salary >= 150000 THEN 'High'
  WHEN salary >= 90000  THEN 'Medium'
  ELSE 'Low'
END

CASE WHEN department = 'Sales' THEN 'Sales team' END

CASE WHEN department = 'Sales' THEN 'Sales team'
     ELSE 'Other team' END

CASE WHEN shipped_date = NULL THEN 'Not shipped'
     ELSE 'Shipped' END

CASE WHEN shipped_date IS NULL THEN 'Not shipped'
     ELSE 'Shipped' END

CASE WHEN salary >= 150000 THEN 'High' ELSE 0 END

CASE WHEN salary >= 150000 THEN 'High' ELSE 'Other' END

CASE WHEN salary > 90000 THEN 'Medium' ELSE 'Low' END

CASE WHEN salary >= 90000 THEN 'Medium' ELSE 'Low' END

CASE in SELECT costs very little: it is evaluated once per row that is already being returned. Two places deserve more care:

Use EXPLAIN to check whether a query uses an index before rewriting it.

6 exercises on the real Playground tables.

Write each query yourself first. Every solution has been run against the Playground database.

1. 1Easy Label every product as "Budget" (under 100), "Mid-range" (100 to under 1,000) or "Premium" (1,000 and up), and count the products in each label. Table: products

   Show solution

   SQLite · runs in the Playground

   SELECT CASE
            WHEN price < 100  THEN 'Budget'
            WHEN price < 1000 THEN 'Mid-range'
            ELSE 'Premium'
          END AS price_band,
          COUNT(*) AS products
   FROM products
   GROUP BY price_band
   ORDER BY products DESC;

   ▶ Run this query in the SQL Playground

   Going from the lowest threshold upward with < works as well as going down with >=; what matters is that the order matches the comparisons.

2. 2Easy Classify reviews as "Positive" (4-5 stars), "Neutral" (3) or "Negative" (1-2) and count each group. Table: reviews

   Show solution

   SQLite · runs in the Playground

   SELECT CASE
            WHEN rating >= 4 THEN 'Positive'
            WHEN rating = 3  THEN 'Neutral'
            ELSE 'Negative'
          END AS sentiment,
          COUNT(*) AS reviews
   FROM reviews
   GROUP BY sentiment
   ORDER BY reviews DESC;

   ▶ Run this query in the SQL Playground

   The rating column is 1 to 5, so ELSE covers exactly 1 and 2.

3. 3Medium For each payment method, show the number of payments and how many of them succeeded and failed, in one row per method. Table: payments

   Show solution

   SQLite · runs in the Playground

   SELECT method,
          COUNT(*) AS payments,
          SUM(CASE WHEN status = 'success' THEN 1 ELSE 0 END) AS succeeded,
          SUM(CASE WHEN status = 'failed'  THEN 1 ELSE 0 END) AS failed
   FROM payments
   GROUP BY method
   ORDER BY payments DESC;

   ▶ Run this query in the SQL Playground

   Conditional aggregation turns the status values into columns, so each method needs only one row.

4. 4Medium Count premium and regular users per country, side by side. Table: users

   Show solution

   SQLite · runs in the Playground

   SELECT country,
          SUM(CASE WHEN is_premium = 1 THEN 1 ELSE 0 END) AS premium,
          SUM(CASE WHEN is_premium = 0 THEN 1 ELSE 0 END) AS regular
   FROM users
   GROUP BY country
   ORDER BY premium + regular DESC;

   ▶ Run this query in the SQL Playground

   Each SUM counts only the rows its CASE lets through; ORDER BY can sort by the sum of the two.

5. 5Medium List the 10 most recent orders with a column "size" that is "Large" for totals of 2,000 or more and "Standard" otherwise. Table: orders

   Show solution

   SQLite · runs in the Playground

   SELECT id, order_date, total,
          CASE WHEN total >= 2000 THEN 'Large' ELSE 'Standard' END AS size
   FROM orders
   ORDER BY order_date DESC, id DESC
   LIMIT 10;

   ▶ Run this query in the SQL Playground

   A two-branch CASE is the portable version of IF() or IIF().

6. 6Hard For each department, show the share of employees (in percent, one decimal) who earn 150,000 or more. Sort from the highest share. Table: employees

   Show solution

   SQLite · runs in the Playground

   SELECT department,
          COUNT(*) AS employees,
          ROUND(100.0 * SUM(CASE WHEN salary >= 150000 THEN 1 ELSE 0 END) / COUNT(*), 1) AS pct_high_earners
   FROM employees
   GROUP BY department
   ORDER BY pct_high_earners DESC, department;

   ▶ Run this query in the SQL Playground

   Multiplying by 100.0 first makes the division decimal; integer division would round every share down to a whole number.