CASE WHEN in SQL Server: label rows for a report

CASE is if-then-else for a column. It checks each WHEN in order and returns the first match, or the ELSE if nothing matches.

Label orders by size

SELECT order_id,
       total_amount,
       CASE
         WHEN total_amount < 50   THEN 'Small'
         WHEN total_amount <= 150 THEN 'Medium'
         ELSE 'Large'
       END AS size
FROM Orders;

Order matters. An order of £40 matches the first WHEN and stops there, so the second WHEN only ever sees £50 and up.

Count per band

SELECT CASE WHEN total_amount < 50 THEN 'Small'
            WHEN total_amount <= 150 THEN 'Medium'
            ELSE 'Large' END AS size,
       COUNT(*) AS order_count
FROM Orders
GROUP BY CASE WHEN total_amount < 50 THEN 'Small'
              WHEN total_amount <= 150 THEN 'Medium'
              ELSE 'Large' END;

SQL Server won’t let you GROUP BY size, so either repeat the CASE or build the label in a CTE.

Conditional totals

SELECT SUM(CASE WHEN status = 'Shipped'   THEN 1 ELSE 0 END) AS shipped,
       SUM(CASE WHEN status = 'Cancelled' THEN 1 ELSE 0 END) AS cancelled
FROM Orders;

IIF(condition, a, b) is a two-way shorthand for a simple CASE.