CASE

Definition

The CASE function allows you to perform conditional statements in BigQuery. This is a more complex implementation of IF.

Syntax

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
colourprimary_colour
bluetrue
pinkfalse
blackfalse

Practical Info

  • 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.
  • Common Questions

    How to create multiple 'THEN' clauses for a case statement?

    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

    How to compare strings ignoring case?

    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

    How to compare dates using a case statement?

    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
    Note: BETWEEN is inclusive so it will return true for both 2020-10-01 AND 2021-09-30.
    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
    date_colfiscal_date
    2020-10-01FY 2021
    2021-09-30FY 2021
    2019-10-01pre FY 2021

    How to Group By / Aggregate by a Case function

    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
    f0_Region
    -1East
    4West

    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
    east_totalwest_total
    -14

    Troubleshooting Common Errors

    Type mismatch for operands of = operator: number and string.

    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' ...

    Case expression returns with mixed types are not supported.

    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
  • IF
  • COALESCE
  • Data Types in Standard SQL
  • Subscribe to newsletter

    Subscribe to receive the latest blog posts to your inbox every week.

    By subscribing you agree to our Privacy Policy.
    Thank you! Your submission has been received!
    Oops! Something went wrong while submitting the form.

    Start solving your organization's biggest problems with Count today.

    Stay up to date with all things count, data and problem solving.