Menu

SQLite avg() Function

SQLite avg() calculates the arithmetic mean of the non-NULL values in an expression. It is useful for average order amounts, exam scores, and per-group statistics, but you must understand how NULL values affect the denominator.

Syntax

avg(expression)
avg(DISTINCT expression)
  • avg(expression) ignores SQL NULL values.
  • avg(DISTINCT expression) averages distinct non-NULL values.
  • The result is an SQL REAL value whenever at least one non-NULL input exists, even if every input is an integer.
  • If there are no non-NULL inputs, the result is SQL NULL.

SQLite treats non-NULL text or BLOB values that do not look numeric as zero when computing avg(). Clean or validate data before averaging a text column.

Examples

Average numbers while ignoring NULL

WITH scores(score) AS (
    VALUES (8), (10), (NULL)
)
SELECT
    count(*) AS rows_total,
    count(score) AS scored_rows,
    avg(score) AS average_score
FROM scores;

rows_total  scored_rows  average_score
----------  -----------  -------------
3           2            9.0

The average is (8 + 10) / 2 = 9, not (8 + 10) / 3. NULL values do not contribute to the sum or the number of values averaged.

Calculate an average for each group

WITH exam_scores(student, subject, score) AS (
    VALUES
        ('Tim', 'Math', 8),
        ('Tim', 'English', 9),
        ('Tom', 'Math', 7),
        ('Tom', 'English', 5)
)
SELECT
    student,
    avg(score) AS average_score
FROM exam_scores
GROUP BY student
ORDER BY student;

student  average_score
-------  -------------
Tim      8.5
Tom      6.0

GROUP BY produces a separate average for each student. The final ORDER BY makes the row order predictable.

Average only distinct values

WITH readings(value) AS (
    VALUES (10), (10), (20)
)
SELECT
    avg(value) AS all_readings,
    avg(DISTINCT value) AS unique_readings
FROM readings;

all_readings        unique_readings
------------------  ---------------
13.3333333333333    15.0

DISTINCT removes a duplicate 10 before taking the mean. The first result is a floating-point approximation, so the number of displayed digits may vary between SQL clients.

What if there are no non-NULL values?

WITH scores(score) AS (
    VALUES (NULL), (NULL)
)
SELECT
    avg(score) IS NULL AS average_is_null,
    coalesce(avg(score), 0) AS display_fallback
FROM scores;

average_is_null  display_fallback
---------------  ----------------
1                0

An undefined average is not necessarily zero. Use coalesce() only when zero is an appropriate presentation fallback for your application.

Frequently asked questions

Does AVG include NULL in the denominator?

No. SQLite avg(x) uses the count of non-NULL values in x, not count(*). See count() for the distinction.

Why does AVG return a decimal when my column contains integers?

SQLite returns a floating-point (REAL) average for any group with a non-NULL input. Round it for display, for example with round(avg(score), 2), if required.

Try it in your browser

Run and edit these examples in the SQLite Playground. For the precise semantics, see the SQLite aggregate-function documentation.