# CASE / WHEN in T-SQL

CASE picks a value by conditions, like if and else if. Use it to label rows, to count only the rows that match with SUM(CASE ...), and to sort in a custom order.

Journeyman · T-SQL · SQL · Updated November 3, 2026

## 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 `WHEN`s 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

```sql
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;
```

| ProductName | UnitsInStock | StockLevel |
|---|---|---|
| Chai | 39 | In stock |
| Chang | 17 | Low |
| Aniseed Syrup | 13 | Low |
| Chef Anton's Cajun Seasoning | 53 | In stock |
| Chef Anton's Gumbo Mix | 0 | Out of stock |
| Grandma's Boysenberry Spread | 120 | In stock |

- The `WHEN`s 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.

```sql
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;
```

| OrderId | ShipVia | Shipper |
|---|---|---|
| 10248 | 3 | Federal Shipping |
| 10249 | 1 | Speedy Express |
| 10250 | 2 | United Package |
| 10251 | 1 | Speedy Express |

(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?

```sql
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;
```

| PriceBand | Products |
|---|---|
| Budget | 39 |
| Standard | 31 |
| Premium | 7 |

## 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:

```sql
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;
```

| Orders | NotShipped | Late |
|---|---|---|
| 830 | 21 | 37 |

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:

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

| ProductName | UnitsInStock |
|---|---|
| Alice Mutton | 0 |
| Chef Anton's Gumbo Mix | 0 |
| Gorgonzola Telino | 0 |
| Perth Pasties | 0 |
| Thüringer Rostbratwurst | 0 |

## 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](https://www.ask-codey.com/glossary/case-expression)

## Cheat sheets

- [CASE / WHEN in T-SQL](https://www.ask-codey.com/cheat-sheets/case-when-in-t-sql-cheat-sheet)

Source: https://www.ask-codey.com/lessons/case-when-in-t-sql