If-then logic with CASE

Label rows, build your own groups, sort in a custom order and count only the rows you want.

CASE is SQL’s if-then. It checks each row against a list of conditions and returns one value per row. You’ll use it to label rows, build groups that don’t exist in the data, sort in a custom order, and count only some rows. The next lesson, on ratios and rates, uses it in almost every query.

The shape

SELECT name,
       amount,
       CASE
         WHEN amount >= 100000 THEN 'Large'
         WHEN amount >= 25000  THEN 'Medium'
         ELSE 'Small'
       END AS deal_size
FROM opportunities;

The whole thing, from CASE to END, is one value. It can go anywhere a column name can.

Rule 1: first match wins

CASE checks the conditions top to bottom and stops at the first TRUE one. Swap the two WHENs above:

CASE
  WHEN amount >= 25000  THEN 'Medium'   -- a $150,000 deal stops here
  WHEN amount >= 100000 THEN 'Large'    -- never reached
  ELSE 'Small'
END

Every deal of $25,000 or more is now Medium, and Large never appears: 38 Medium deals instead of 31 Medium and 7 Large. No error, just a wrong answer. Put the most specific condition first.

Rule 2: no ELSE means NULL

A row that matches no WHEN gets NULL when there’s no ELSE:

SELECT CASE
         WHEN lead_source IN ('Inbound', 'Webinar')              THEN 'Marketing'
         WHEN lead_source IN ('Outbound', 'Referral', 'Partner') THEN 'Sales'
       END AS channel,
       COUNT(*) AS deals
FROM opportunities
GROUP BY channel;

That returns 22 Marketing, 33 Sales and 15 NULL. The 15 are deals with no lead source. NULL IN (...) is never TRUE (lesson 5), so they fall through every WHEN. Add ELSE 'Unknown' to give them a label you chose on purpose.

Rule 3: every THEN returns the same type

CASE WHEN amount >= 100000 THEN amount ELSE 'n/a' END   -- number in one branch, text in the other

SQL Server and PostgreSQL stop with an error. SQLite quietly returns a mix, which breaks sorting and math later. Keep every branch a number, or every branch text.

The short form

When every test is “does this one column equal a value”, you can name the column once:

CASE stage
  WHEN 'Prospecting'   THEN 1
  WHEN 'Qualification' THEN 2
  WHEN 'Proposal'      THEN 3
  WHEN 'Negotiation'   THEN 4
END

That’s the same as writing WHEN stage = 'Prospecting' on each line. One catch: CASE lead_source WHEN NULL THEN ... never matches, because it means lead_source = NULL. For NULL, use the long form with WHEN lead_source IS NULL.

Where CASE goes

In GROUP BY: groups that aren’t in the data

There’s no deal_size column, but you can group by one you build:

SELECT CASE
         WHEN amount >= 100000 THEN 'Large'
         WHEN amount >= 25000  THEN 'Medium'
         ELSE 'Small'
       END AS deal_size,
       COUNT(*) AS deals
FROM opportunities
GROUP BY deal_size;

Grouping by the alias works in SQLite, PostgreSQL and MySQL. SQL Server doesn’t allow it, so there you repeat the whole CASE in GROUP BY.

In ORDER BY: a custom sort

Sorting stages alphabetically puts Closed Lost first. Sort them in pipeline order instead:

SELECT stage, COUNT(*) AS deals
FROM opportunities
GROUP BY stage
ORDER BY CASE stage
           WHEN 'Prospecting'   THEN 1
           WHEN 'Qualification' THEN 2
           WHEN 'Proposal'      THEN 3
           WHEN 'Negotiation'   THEN 4
           WHEN 'Closed Won'    THEN 5
           WHEN 'Closed Lost'   THEN 6
         END;

The numbers never show up in the result. They only decide the order.

Inside an aggregate: count only some rows

COUNT(*) counts every row in a group. To count a subset, make each row a 1 or a 0 and add them up:

SELECT owner_id,
       COUNT(*)                                              AS deals,
       SUM(CASE WHEN is_won = 1         THEN 1 ELSE 0 END)   AS won,
       SUM(CASE WHEN is_won = 0         THEN 1 ELSE 0 END)   AS lost,
       SUM(CASE WHEN closed_at IS NULL  THEN 1 ELSE 0 END)   AS still_open
FROM opportunities
GROUP BY owner_id;

One row per rep, several counts side by side. This pattern is called conditional aggregation, and it’s behind most report queries. Swap THEN 1 for THEN amount and you get won revenue instead of a won count.

Common mistake: COUNT(CASE WHEN is_won = 1 THEN 1 ELSE 0 END) counts every row, because COUNT counts anything that isn’t NULL, and 0 isn’t NULL. Use SUM with ELSE 0.

Dialect notes

CASE works everywhere, so learn it first and use the shortcuts once you know which database you’re on.

Snowflake notes

Snowflake stops with an error when THEN branches mix types, and it lets you GROUP BY the CASE alias.

More in the field guide: CASE WHEN, Conditional aggregation and the CASE breakdown.

Try it

How is the open pipeline spread across deal sizes? Label each open deal Large (100,000 or more), Medium (25,000 or more) or Small (everything else). Return the label, the number of deals and their total amount, with the biggest total first.

Stretch: field guide exercise I1 builds the won, lost and open counts per rep from the conditional aggregation section.

Write it in the sandbox below, then press Check answer.

Back to the top of the lesson