CASE WHEN

SELECT name, salary,
  CASE
    WHEN salary > 80000 THEN 'High'
    WHEN salary > 40000 THEN 'Mid'
    ELSE 'Low'
  END AS salary_band
FROM employees;

CASE WHEN evaluates conditions top to bottom and returns the first match. ELSE handles any row that doesn't match earlier conditions. Without ELSE, unmatched rows return NULL.

SELECT
  SUM(CASE WHEN salary > 80000 THEN 1 ELSE 0 END) AS high,
  SUM(CASE WHEN salary BETWEEN 40000 AND 80000 THEN 1 ELSE 0 END) AS mid,
  SUM(CASE WHEN salary < 40000 THEN 1 ELSE 0 END) AS low
FROM employees;

Conditional aggregation using CASE inside SUM. Returns one row with three columns instead of three rows — a pivot-style output used in MIS reporting.

SELECT
  SUM(CASE WHEN DAYOFWEEK(order_date) IN (1,7) THEN amount ELSE 0 END) AS weekend_rev,
  SUM(CASE WHEN DAYOFWEEK(order_date) NOT IN (1,7) THEN amount ELSE 0 END) AS weekday_rev
FROM orders;

DAYOFWEEK returns 1=Sunday, 7=Saturday in MySQL. Conditional aggregation is the standard way to pivot/split data without multiple queries.

SELECT order_id, amount,
  CASE
    WHEN amount > 5000 THEN amount * 0.10
    WHEN amount > 2000 THEN amount * 0.05
    ELSE 0
  END AS discount
FROM orders;

CASE WHEN works in any part of SELECT. Conditions are checked in order — since amount > 5000 is checked first, those rows won't also match amount > 2000.

SELECT name,
  CASE
    WHEN department = 'Sales' THEN 'Customer Facing'
    WHEN department IN ('IT', 'Engineering') THEN 'Tech'
    ELSE 'Support'
  END AS dept_category
FROM employees;

CASE WHEN lets you reclassify or relabel values on the fly without changing the underlying data — useful for reporting where business needs different groupings than the data stores.