CASE is SQL's conditional expression. The searched form, CASE WHEN price >= 40 THEN 'premium' WHEN price >= 20 THEN 'standard' ELSE 'budget' END, returns the first true branch, so order branches from the most restrictive. The simple form, CASE category_id WHEN 5 THEN 'merch' END, compares one value with a list. A miss without ELSE gives NULL, and IF(cond, a, b) is MySQL 524 's two-way shorthand. A condition inside an aggregate pivots a column's values into columns:
SELECT c.name,
COUNT(CASE WHEN o.status = 'shipped' THEN 1 END) AS shipped,
SUM(o.status = 'paid') AS paid,
IF(COUNT(*) > 1, 'repeat', 'new') AS buyer
FROM customers AS c JOIN orders AS o ON o.customer_id = c.id
WHERE c.id BETWEEN 3 AND 5
GROUP BY c.id;+-------------+---------+------+--------+ | name | shipped | paid | buyer | +-------------+---------+------+--------+ | Chen Wei | 0 | 1 | repeat | | Dara Okafor | 0 | 0 | new | | Elif Yilmaz | 1 | 0 | new | +-------------+---------+------+--------+ 3 rows in set (0.001 sec)
COUNT(CASE WHEN ... THEN 1 END) is portable: non-matching rows become NULL, which COUNT skips. SUM(o.status = 'paid') is a MySQL shortcut, since a comparison yields 1 or 0. CASE x WHEN NULL never matches; write CASE WHEN x IS NULL. Pivot columns are fixed in the query text, so for data-driven columns build the SQL in PHP (PHP) from whitelisted values.