AVG()

Syntax

How it works

AVG() returns the arithmetic mean of the non-NULL values in a group. It is equivalent to SUM() divided by COUNT() of the same non-NULL values, and like the other aggregates it pairs with GROUP BY to give one average per group.

The key subtlety is that AVG ignores NULLs in both the total and the count. If you want NULLs to count as zero, do not use AVG directly: divide an explicit SUM by COUNT(*) instead, so the missing values still pull the average down.

Watch the data type. In some engines, averaging an integer column can perform integer division and truncate the result. Cast to a decimal type when you need fractional precision.

Examples

Average of a column

SELECT AVG(total) AS avg_order_value
FROM orders;

avg_order_value
---------------
      184.28

Average per group

SELECT country, AVG(total) AS avg_order
FROM orders
GROUP BY country
ORDER BY avg_order DESC;

country | avg_order
--------+----------
 UAE    |    201.7
 KSA    |    173.4

Force fractional precision

-- avg of an integer column, kept fractional
SELECT AVG(CAST(rating AS DECIMAL(5,2))) AS avg_rating
FROM reviews;

avg_rating
----------
     4.37

Moving average as a window function

SELECT order_date, total,
       AVG(total) OVER (
         ORDER BY order_date
         ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
       ) AS moving_avg_3
FROM orders
ORDER BY order_date;

order_date | total | moving_avg_3
-----------+-------+-------------
 2026-01-02|  120  |       120.0
 2026-01-03|   80  |       100.0
 2026-01-05|  260  |       153.3

Common mistakes

-- NULL ratings are skipped, so this average is
-- only over the reviews that HAVE a rating
SELECT AVG(rating) FROM reviews;

-- Treat missing ratings as 0 in the average
SELECT SUM(rating) * 1.0 / COUNT(*) FROM reviews;

-- Integer division can truncate to a whole number
SELECT AVG(score) FROM games;   -- e.g. 7 instead of 7.5

SELECT AVG(score * 1.0) FROM games;   -- 7.5

Performance

AVG() is a single pass, just like SUM and COUNT, so its cost tracks the number of rows scanned. Filter and index the columns in WHERE and GROUP BY to keep it fast; the average itself is negligible.

For a weighted average, compute it explicitly as SUM(value * weight) / SUM(weight) rather than AVG, which only gives an unweighted mean.

Interview questions

SELECT AVG(score * 1.0) AS avg_score FROM games;