COUNTIF and conditional aggregation

Pass rates, failure rates and shares of a total are the numbers people quote. You get them by counting only the rows that meet a condition.

How it works

COUNTIF(condition) counts the rows in each group where the condition is true. Dividing it by COUNT(*) gives the share of rows that meet the condition, so one pass over the table produces both numbers.

Common mistake

A rate needs the same rows in the numerator and the denominator. Adding WHERE test_result = 'PASSED' to the whole query makes every rate 1, because the failed tests are gone before COUNT(*) sees them.

Worked example

SELECT vehicle_snapshot.fuel_type, COUNTIF(test_result = 'FAILED') AS failed, COUNT(*) AS total
FROM `learn.mot.fact_mot_test`
WHERE completed_date BETWEEN '2024-08-01' AND '2024-08-31'
GROUP BY vehicle_snapshot.fuel_type
ORDER BY fuel_type

Each fuel type gets two counts from the same pass over the August 2024 tests.

failed counts only the rows where test_result is 'FAILED', and total counts every row in the group.

All BigQuery lessons