CASE WHEN statements provide great flexibility when dealing with buckets of results or when you need to find a way to filter out certain results. Another way to think of it is it's a conditional logic similar to IF-THEN statements in other programming languages.
When using a CASE WHEN statement, it's important to remember you need a condition, what to do when that condition is met, and an END clause. A simple syntax is below:
CASE
WHEN condition
THEN result
ELSE other_result
ENDYou can include multiple conditional buckets as needed by adding more WHEN and THEN clauses to your statement, as follows:
SELECT
CASE WHEN condition THEN result WHEN condition_2 THEN result_2
WHEN condition_3
THEN result_3 ELSE other_result END AS new_column_name
For example,
SELECT
CASE
WHEN country = 'USA'
THEN 'North America'
WHEN country = 'Mexico'
THEN 'North America'
ELSE country
END AS 'West Region'
Operators:
- Equals: =
- Not Equals: !=
- Greater than: >
- Less than: <
- Greater or equal: >=
- Less or equal: <=
- Contains: "Like"
- Does not contain: "Not Like"
Aggregations Inside Case Statements
Aggregate functions can be used inside a CASE statement, both in the WHEN conditions and in the THEN and ELSE results. SUM, AVG, COUNT, MIN and MAX are supported, along with the other aggregates such as MEDIAN, PERCENTILE and SD.
A CASE containing an aggregate is evaluated once per group, after the grouping has been applied, so it buckets the aggregated values rather than the individual rows:
SELECT
region,
CASE
WHEN sum(revenue) > 100000
THEN 'High'
WHEN sum(revenue) > 50000
THEN 'Medium'
ELSE 'Low'
END AS tier,
sum(revenue) AS total
GROUP BY region
Aggregates can also be returned by the THEN and ELSE clauses, and a condition can be an expression over more than one aggregate:
SELECT region,
CASE WHEN sum(revenue) / count(*) > 50000 THEN sum(revenue) ELSE 0 END AS big
GROUP BY region
Regular fields can be mixed in with the aggregates in the same statement:
SELECT region,
CASE WHEN region = 'east' AND sum(revenue) > 100000 THEN 'Top east' ELSE 'Other' END AS tier
GROUP BY region
Without a GROUP BY clause, a CASE containing an aggregate creates its own implicit group, so the query returns a single row for the whole result set:
SELECT CASE WHEN sum(revenue) > 100000 THEN 'High' ELSE 'Low' END AS tier
When the alias of a CASE is also the name of an input field, that name keeps meaning the input field for the WHERE clause and for the other select fields:
SELECT CASE WHEN opened > 1000 THEN 'test' ELSE 'test1' END AS customer WHERE customer = 'Costco' -- filters on the input customer field, not on the CASE result
Give the CASE an alias that no input field uses and the name resolves to the CASE result instead:
SELECT CASE WHEN opened > 1000 THEN 'test' ELSE 'test1' END AS customer1 WHERE customer1 = 'test' -- customer1 is only the CASE, so this filters on its result
The GROUP BY clause is the exception: it groups by the CASE result even when the alias shadows an input field, while the WHERE clause and the other select fields still read the input field.
A few things to keep in mind:
- The result of a CASE containing aggregates only exists after the grouping, so it cannot be referenced by the WHERE clause, by the GROUP BY clause or by another select field. CASE statements without aggregates are unchanged and can still be referenced by all of them.
- Aggregates are not allowed in a CASE nested inside an aggregate function.
- If the WHERE clause filters out every row, the aggregates have no value, so the CASE has no result and no row is returned, the same way a plain aggregate select behaves. An aggregate that still returns a value over no rows, such as COUNT, is a result like any other and the CASE is evaluated against it.