aggregate
AVG
Computes the arithmetic mean of non-NULL numeric values. NULL rows are excluded from both the sum and the divisor, so AVG is not equivalent to SUM/COUNT(*) when NULLs are present.
AVG([DISTINCT] numeric_expression)Parameters / Modifiers
| Parameter | Purpose |
|---|---|
| DISTINCT | Average of unique values only |
| OVER (...) | Moving averages when used as a window function |
| FILTER (WHERE ...) | Postgres: only include rows matching a predicate |
Examples
SELECT AVG(price) FROM products;Mean price across all non-NULL prices
SELECT ROUND(AVG(rating), 2) FROM reviews;Mean rating rounded to two decimal places
SELECT category, AVG(price) FROM products GROUP BY category;Average price per category
SELECT AVG(score) OVER (PARTITION BY class_id) FROM tests;Class average attached to each row
Dialect notes / Gotcha
In Postgres, AVG on an integer column returns numeric; in MySQL it returns a DECIMAL / DOUBLE. Empty groups return NULL — coalesce if a numeric default is required.
Related functions
SUM
Adds up all non-NULL numeric values in a column or expression. Returns NULL (not 0) if every input row is NULL or the result set is empty.
ROUND
Rounds a numeric value to the specified number of decimal places (default 0). Uses banker's rounding (half-to-even) on Postgres numeric; half-away-from-zero on MySQL and float types.
COUNT
Returns the number of rows matching a query, optionally counting non-NULL values of an expression or distinct values. COUNT(*) counts every row including NULLs, while COUNT(expr) skips NULLs.
MIN
Returns the smallest non-NULL value in a group, using natural ordering for the column's data type. Works for numbers, dates, and strings (lexicographic).
MAX
Returns the largest non-NULL value in a group by the column's natural ordering. Useful for latest timestamps, top prices, or alphabetically last strings.
← All SQL functions · SQL cheat sheet · Window functions · PostgreSQL cheat sheet