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.
SUM(x)adds up the values andAVG(x)averages them. Both skipNULLvalues, so a test with no reading adds nothing to the sum and does not count in the average.ROUND(x)rounds to a whole number andROUND(x, 2)rounds to two decimal places.HAVINGkeeps the groups whose aggregate result passes a test, such asCOUNT(*) >= 150.- A small group gives a shaky average, so a threshold such as
HAVING COUNT(*) >= 150keeps the result meaningful.
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_typeThe 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.