How to Use CASE Statements in SQL Server

Branches are evaluated top to bottom and the first match wins, so order matters: putting the 20 test before the 100 test would label every large order medium. Without an ELSE, unmatched rows return NULL rather than raising an error. All branches must return compatible types, and data type precedence means a mixed int and varchar CASE can fail at runtime.

SELECT id, amount,
  CASE
    WHEN amount >= 100 THEN 'large'
    WHEN amount >= 20  THEN 'medium'
    ELSE 'small'
  END AS order_size
FROM orders;

Conditional aggregation

Conditional aggregation is the most valuable use of CASE, turning rows into columns without a PIVOT. Note the difference between the two lines: SUM needs the ELSE 0 so non-matching rows contribute nothing, while COUNT ignores NULLs automatically, so COUNT(CASE WHEN ... THEN 1 END) counts only the matches and needs no ELSE at all.

SELECT customer_id,
       SUM(CASE WHEN status = 'refunded' THEN amount ELSE 0 END) AS refunded_total,
       COUNT(CASE WHEN status = 'shipped' THEN 1 END) AS shipped_orders
FROM orders
GROUP BY customer_id;