CASE / WHEN in T-SQL

After this lesson you can

• Turn values into labels with a CASE expression, searched or simple
• Count or total only the rows that match, with SUM(CASE ...)

Key takeaways

• CASE checks its WHENs in order and returns the value of the first true one; it ends with END.
• Rows that no WHEN matches get the ELSE value, or NULL when there's no ELSE.
• A searched CASE has a full condition after each WHEN; a simple CASE names one column and lists the values to match.
• SUM(CASE WHEN ... THEN 1 ELSE 0 END) counts only the rows that match, and several such counts fit in one query.
• CASE also works in GROUP BY to count rows per label, and in ORDER BY to sort in your own order.

CASE is SQL's if ... else if ... else. It checks conditions in order, and returns the value of the first one that's true.

Labels from conditions

SELECT TOP (6) ProductName
    , UnitsInStock
    , CASE
        WHEN UnitsInStock = 0 THEN 'Out of stock'
        WHEN UnitsInStock < 20 THEN 'Low'
        ELSE 'In stock'
      END AS StockLevel
FROM Products
ORDER BY ProductId;

• The WHENs are checked in order, and the first true one wins. Chef Anton's Gumbo Mix has 0, which is also less than 20, but = 0 comes first.
• ELSE covers every row no WHEN matched. Without an ELSE, those rows get NULL.
• CASE ends with END, and usually gets an alias.

This form, with a full condition after each WHEN, is a searched CASE.

Simple CASE: one value, many matches

When every condition compares the same column with =, the simple form is shorter: name the column once, then list the values.

SELECT TOP (4) OrderId
    , ShipVia
    , CASE ShipVia
        WHEN 1 THEN 'Speedy Express'
        WHEN 2 THEN 'United Package'
        WHEN 3 THEN 'Federal Shipping'
        ELSE 'Unknown'
      END AS Shipper
FROM Orders
ORDER BY OrderId;

(Here, a join to the Shippers table would be better: the names belong in the database, not in your query. CASE shines when the labels are your own, such as price bands.)

Grouping by a CASE

Group by the CASE, and you can count rows per label. How many products are in each price band?

SELECT CASE
        WHEN UnitPrice < 20 THEN 'Budget'
        WHEN UnitPrice < 50 THEN 'Standard'
        ELSE 'Premium'
       END AS PriceBand
    , COUNT(*) AS Products
FROM Products
GROUP BY CASE
        WHEN UnitPrice < 20 THEN 'Budget'
        WHEN UnitPrice < 50 THEN 'Standard'
        ELSE 'Premium'
       END
ORDER BY Products DESC;

Counting only some rows: SUM(CASE ...)

A CASE that returns 1 or 0, added up with SUM, counts the rows that match, and several counts fit in one query:

SELECT COUNT(*) AS Orders
    , SUM(CASE WHEN ShippedDate IS NULL THEN 1 ELSE 0 END) AS NotShipped
    , SUM(CASE WHEN ShippedDate > RequiredDate THEN 1 ELSE 0 END) AS Late
FROM Orders;

One pass over the table, three answers. With WHERE, each would need its own query.

Sorting in your own order

CASE works in ORDER BY too. Products that are out of stock first, then the rest, each part by name:

SELECT TOP (5) ProductName, UnitsInStock
FROM Products
ORDER BY CASE WHEN UnitsInStock = 0 THEN 0 ELSE 1 END, ProductName;

Check yourself

2 questions to try on the lesson page.

So what

CASE brings if and else into SQL: labels people understand, counts of exactly the rows you care about, and orders no column could give you, all without leaving the query.

Key terms

• CASE Expression

Cheat sheets

• CASE / WHEN in T-SQL