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.
COUNTIF(test_result = 'PASSED')counts the rows in the group where the condition is true. It is a shorter way to writeSUM(CASE WHEN condition THEN 1 ELSE 0 END).SAFE_DIVIDE(a, b)dividesabyband never stops the query for a zerob. The operatora / bdoes.ROUND(x, 3)keeps three decimal places, so 0.71468 becomes 0.715.
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_typeEach 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.