SQL Reference ยท Aggregate Functions
SQL AVG() function
Reference for the SQL AVG() aggregate: average of numbers per table or group, NULL handling, integer division traps, DISTINCT and weighted averages.
AVG(expr) is an aggregate function that returns the arithmetic mean of the non-NULL values of expr in a group of rows. Without GROUP BY the whole result is one group; with GROUP BY you get one average per group.
The mean is the sum of the values divided by how many values there were. NULL values are skipped entirely: they do not count as zero and they do not appear in the divisor. That is usually what you want, and occasionally a surprise.
SELECT AVG(price) AS average_price FROM books;
Try it
Press Run (or Ctrl + Enter) to see the result here.
Press Run (or Ctrl + Enter) to see the result here.
Press Run (or Ctrl + Enter) to see the result here.
Syntax
AVG([ALL | DISTINCT] expr) AVG(expr) OVER (...) -- as a window function
| Part | Description |
|---|---|
expr |
A numeric expression, usually a column. Rows where it is NULL are ignored. |
ALL |
The default: average every non-NULL value, duplicates included. |
DISTINCT |
Average each distinct value once. |
Returns: the mean of the non-NULL values, or NULL if there are none (an empty set, or only NULLs). The result type depends on the database; see below.
Dialect differences
| Database | Result type and notes |
|---|---|
| SQLite | Always a floating-point number (or NULL). Text that looks like a number is converted; other text counts as 0. |
| PostgreSQL | AVG(integer) returns numeric, so AVG of 1 and 2 is 1.5000000000000000. AVG(double precision) returns double precision. |
| MySQL | AVG of an integer returns a DECIMAL with a few extra digits; of a float, a DOUBLE. |
| SQL Server | AVG of an int column returns an int, truncated: the average of 1 and 2 is 1. Cast first. |
| Oracle | Returns NUMBER, a decimal average. |
-- SQL Server: avoid integer truncation SELECT AVG(CAST(quantity AS DECIMAL(10, 2))) FROM orders;
Usage notes
- NULLs are ignored. If you want missing values to count as zero, say so:
AVG(COALESCE(x, 0)). This changes the answer, so choose deliberately. - Empty groups.
AVGover no rows isNULL, not 0.COALESCE(AVG(x), 0)converts it if you need a number. - Average of averages. The mean of per-group averages is not the overall mean unless every group has the same size. Compute
SUM(x) / COUNT(x)over the raw rows instead. - Weighted average. Use
SUM(value * weight) / SUM(weight), notAVG. DISTINCT.AVG(DISTINCT price)averages each distinct price once; rarely what a report needs.- Rounding.
AVGreturns many decimal places; wrap it in ROUND for display, not for later calculations. - Filtering on it. Use
HAVING AVG(price) > 12, sinceWHEREruns before aggregation. - Other statistics. For the middle value see statistical aggregates; for a mean over a sliding frame use a window function.