Expressions, aliases and CASE

Analysts rarely want a raw column. They want a label, a calculated value, or a band: a name for a range of numbers, such as High mileage for every reading over 150000.

How it works

An expression computes a new value for every row that reaches SELECT, and AS gives that value a name. CASE picks one of several values by checking its conditions from top to bottom and stopping at the first one that is true.

Common mistake

A NULL odometer reading fails odometer.value_normalized_km > 150000, so ELSE labels it like an ordinary reading. Put an IS NULL branch before the comparisons when missing values need a label of their own.

Worked example

SELECT vehicle_id,
  CASE WHEN test_result = 'PASSED' THEN 'ready to sell'
       WHEN test_result = 'FAILED' THEN 'needs work'
  END AS verdict
FROM `learn.mot.fact_mot_test`
WHERE make = 'HONDA'
  AND completed_date BETWEEN '2024-03-01' AND '2024-03-31'

Each test gets a verdict from its result. The first WHEN that is true decides the value.

Every row has one of the two results, so no verdict is NULL here.

All BigQuery lessons