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
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
CASEchecks itsWHENs in order and returns the value of the first true one; it ends withEND.- Rows that no
WHENmatches get theELSEvalue, orNULLwhen there's noELSE. - A searched
CASEhas a full condition after eachWHEN; a simpleCASEnames 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.CASEalso works inGROUP BYto count rows per label, and inORDER BYto 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;
| 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
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= 0comes first. ELSEcovers every row noWHENmatched. Without anELSE, those rows getNULL.CASEends withEND, 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;
| 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?
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:
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:
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, namedStatus. - 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.