Skip to content
Tutorials Center
My learning

Topics

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.

SQL
SELECT AVG(price) AS average_price
FROM books;

Try it

SQL Average price overall and per genre ยท database: bookstore

Press Run (or Ctrl + Enter) to see the result here.

SQL NULLs are skipped, not treated as zero ยท database: bookstore

Press Run (or Ctrl + Enter) to see the result here.

SQL Integer inputs still give a fractional average in SQLite ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

Syntax

SQL
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
-- 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. AVG over no rows is NULL, 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), not AVG.
  • DISTINCT. AVG(DISTINCT price) averages each distinct price once; rarely what a report needs.
  • Rounding. AVG returns many decimal places; wrap it in ROUND for display, not for later calculations.
  • Filtering on it. Use HAVING AVG(price) > 12, since WHERE runs before aggregation.
  • Other statistics. For the middle value see statistical aggregates; for a mean over a sliding frame use a window function.