CASE
The CASE function allows you to perform conditional statements in BigQuery. This is a more complex implementation of IF.
CASE expr WHEN expr_to_match THEN result [...] [ELSE else_result] END
SELECT colour, CASE WHEN colour IN ('blue', 'red', 'yellow') THEN true ELSE false END AS primary_colour FROM ( SELECT 'blue' AS colour UNION ALL ( SELECT 'pink' AS colour) UNION ALL ( SELECT 'black' AS colour) ) AS table_3
- If you leave out an else_result, then the query will return NULL if not matched to the result
- result and else_result must be coercible to a common supertype.
To add multiple expression -> result pairings, simply add another WHEN ... THEN clause (without commas) to your function.
Using the example data above, we could use the following query to identify which are the primary colours:
CASE WHEN colour ='blue' THEN true
WHEN colour = 'red' THEN true
WHEN colour = 'yellow' then true
ELSE false
END AS primary_colour
To account for strings being of different cases, we can use UPPER and LOWER to standardise all of the cases in both our expressions to match, and the match criteria.
Using the data above, we could modify our case statement as follows:
CASE
WHEN UPPER(colour) IN ('BLUE', 'RED', 'YELLOW') THEN true
ELSE false
END AS primary_colour
As long as your expressions are of the right type you can use comparison operators (e.g. >, <, >=) to compare dates:
CASE
WHEN date_col BETWEEN '2020-10-01' and '2021-09-30' THEN 'FY 2021'
WHEN date_col<'2020-10-01' then 'pre FY 2021'
END fiscal_date
SELECT
date_col,
CASE WHEN (date_col BETWEEN '2020-10-01' AND '2021-09-30') THEN 'FY 2021' WHEN (date_col < '2020-10-01') THEN 'pre FY 2021' END AS fiscal_date
FROM
(
SELECT
'2020-10-01' AS date_col
UNION ALL
( SELECT
'2021-09-30' AS date_col)
UNION ALL
( SELECT
'2019-10-01' AS date_col)
) AS table_3
To group by the results of a CASE function, just make sure you add the name of the column to your GROUP BY clause.
SELECT
SUM(number),
CASE WHEN city IN ('New York', 'Miami') THEN 'East' ELSE 'West' END AS Region
FROM
(
SELECT
2 AS number,
'New York' AS city
UNION ALL
( SELECT
4 AS number,
'Los Angeles' AS city)
UNION ALL
( SELECT
(-3) AS number,
'Miami' AS city)
) AS table_3
GROUP BY
Region
To aggregate by a case column, make sure your results are numbers:
SELECT
SUM(CASE WHEN city IN ('New York', 'Miami') THEN number ELSE 0 END) AS east_total,
SUM(CASE WHEN city IN ('Los Angeles') THEN number ELSE 0 END) AS west_total
FROM
(
SELECT
2 AS number,
'New York' AS city
UNION ALL
( SELECT
4 AS number,
'Los Angeles' AS city)
UNION ALL
( SELECT
(-3) AS number,
'Miami' AS city)
) AS table_3
This happens when your conditional expression is invalid. For example:
CASE WHEN number_column = 'string' ....
You can resolve this by making sure your condition column and criteria are compatible types:
CASE WHEN CAST(number_column as STRING) = 'string' ...
This error occurs when your result types are different. In this example returning 1 in one case and 'hello' in another won't work.
CASE WHEN (CAST(bq.Avg_monthly_searches AS STRING) = 'total') THEN 1 ELSE 'hello' END
To resolve this, make sure your result types are compatible and coercible.
CASE WHEN (CAST(bq.Avg_monthly_searches AS STRING) = 'total') THEN '1' ELSE 'hello' END
Get five free SQL lessons from Count's Head of Data, Taylor Brownlow.
- WITH CTEs, you can go far
- Not always there when you call, but always on time
- Time to join the JOIN movement
- Everyone's favorite SQL cheat code
- What can UDFs do for you?
Start learning today