MAX_BY, LOGICAL_AND and STRING_AGG

COUNTIF counts the rows that meet a condition. Other questions about a group need a different aggregate, such as the result of its latest test or a list of its values.

How it works

Each group is a set of rows. MAX_BY(x, y) finds the row with the largest y and returns its x. LOGICAL_AND and LOGICAL_OR reduce the true and false values of the group to one value, and STRING_AGG writes its values as one text.

Common mistake

A result can look right and still be one of several possible answers. ANY_VALUE on a column that differs inside the group, a MAX_BY with tied keys and a STRING_AGG with no ORDER BY can each give another answer on the next run.

Worked example

SELECT vehicle_snapshot.fuel_type AS fuel_type,
  MAX(odometer.value_normalized_km) AS highest_km,
  MAX_BY(vehicle_snapshot.model, odometer.value_normalized_km) AS model_at_highest_km,
  STRING_AGG(DISTINCT make, ', ' ORDER BY make LIMIT 3) AS first_makes
FROM `learn.mot.fact_mot_test`
WHERE completed_date BETWEEN '2024-10-01' AND '2024-10-31'
GROUP BY vehicle_snapshot.fuel_type
ORDER BY fuel_type

Each fuel type of the October 2024 tests gets its highest reading, the model of the test that had it, and up to three makes in alphabetical order.

MAX and MAX_BY look at the same column, so model_at_highest_km is the model that goes with highest_km.

All BigQuery lessons