# 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 15, 2026

By Gregor Dzierzon

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

## Do it: label each order on time, late or not shipped

The shipping manager wants every order labeled with how its shipping went, in words anyone can read.

The `Orders` table has each order's `OrderId`, its `RequiredDate` (the day the customer needs it by) and its `ShippedDate` (the day it left, or `NULL` when it hasn't left yet). It holds these rows (the **Tables** tab shows the SQL that makes it):

| OrderId | RequiredDate | ShippedDate |
|---|---|---|
| 10248 | 2016-08-01 | 2016-07-16 |
| 10249 | 2016-08-16 | 2016-07-10 |
| 10264 | 2016-08-21 | 2016-08-23 |
| 10271 | 2016-08-29 | 2016-08-29 |
| 10280 | 2016-09-11 | 2016-09-12 |
| 11008 | 2018-05-06 | NULL |
| 11019 | 2018-05-11 | NULL |

Each order gets one label, the first rule that fits:

| When | Label |
|---|---|
| `ShippedDate` is `NULL` | `Not shipped` |
| `ShippedDate` is later than `RequiredDate` | `Late` |
| Any other order | `On time` |

For example, order 10264 was needed by 2016-08-21 and shipped on 2016-08-23, two days later, so its label is `Late`. Order 10271 shipped on the very day it was needed, which isn't later, so it's `On time`.

Your query should give exactly these rows, in this order:

| OrderId | Status |
|---|---|
| 10248 | On time |
| 10249 | On time |
| 10264 | Late |
| 10271 | On time |
| 10280 | Late |
| 11008 | Not shipped |
| 11019 | Not shipped |

**Your job:** write one `SELECT` query on `Orders`, with a `CASE` expression for the label.

- Show two columns: `OrderId`, then the label, named `Status`.
- Give each order the label from the table above, checking the rules in that order.
- Spell the labels exactly as they are: `Not shipped`, `Late`, `On time`.
- Sort the rows by `OrderId`, smallest first.

Practiced live on the lesson page: write the SQL right there and check it.

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