AVG

Average a set of values, and know how NULL and integer division change the result.

Syntax

AVG(expression)

What it does

AVG() returns the mean of the non-NULL values in a group: SUM(expression) / COUNT(expression). Rows where the expression is NULL are excluded from both the sum and the count.

Example

SELECT
  product_id,
  AVG(rating) AS avg_rating
FROM reviews
GROUP BY product_id;

Important behavior

NULL values are ignored, not treated as 0 — AVG of 10, NULL, 20 is 15, not 10
if every value is NULL, the result is NULL
to average with NULL counted as zero, use AVG(COALESCE(expression, 0)) or SUM(expression) / COUNT(*)
on integer columns some engines do integer division — cast to a decimal type when you need fractional precision

Common gotchas

an unfiltered AVG over a table with many missing values can look higher or lower than expected because of the excluded rows
averaging a column that is itself an average ("average of averages") is not the same as the overall average — weight it instead

Safety checklist

decide whether missing values should be excluded or counted as zero
confirm the column type does not force integer division
verify the denominator is the row set you intend (COUNT(expression) vs COUNT(*))