SUM, AVG and HAVING

A count says how many. Sums and averages say how much, and HAVING keeps only the groups that are big enough to trust.

How it works

Rows are grouped first. SUM and AVG then reduce each group to one number, and HAVING removes the groups whose result fails your test.

Common mistake

AVG divides by the rows that have a value, so it can differ from SUM divided by COUNT(*). A threshold that is too low lets small, shaky groups through.

Worked example

SELECT vehicle_snapshot.fuel_type, COUNT(*) AS test_count,
  ROUND(AVG(odometer.value_normalized_km)) AS avg_km
FROM `learn.mot.fact_mot_test`
WHERE completed_date BETWEEN '2024-09-01' AND '2024-09-30'
GROUP BY vehicle_snapshot.fuel_type
HAVING COUNT(*) >= 25
ORDER BY fuel_type

The September 2024 tests are grouped by fuel type, and each group gets a count and an average reading.

HAVING drops the fuel types with fewer than 25 tests, so a rare fuel type does not show a shaky average.

All BigQuery lessons