AVG()

Example Schema and Data

Some of the examples are based on the temperatures table:

CREATE TABLE temperatures (
  location VARCHAR(255),
  year INT,
  temp INT
);

INSERT INTO temperatures VALUES
  -- 2020
  ('Berlin',2020,-1), ('Berlin',2020,15),
  ('China',2020,15), ('China',2020,0),
  ('Denmark',2020,0), ('Denmark',2020,-10), ('Denmark',2020,10),
  ('Paris',2020,10), ('Paris',2020,5), ('Paris',2020,-1),
  -- 1920
  ('Berlin',1920,-10), ('China',1920,NULL), ('Denmark',1920,-1),
  ('Paris',1920,20),
  -- 1820
  ('Berlin',1820,NULL), ('China',1820,NULL), ('Denmark',1820,NULL),
  ('Paris',1820,NULL);

ALL Clause (default behavior)

The ALL clause is the default behavior for AVG(), applied when DISTINCT is not specified. In this example, all numeric temperature values are used to compute the average.

The values to be averaged:

SELECT *
 FROM temperatures
 WHERE year = 2020
 ORDER BY temp, location;

+----------+------+------+
| location | year | temp |
+----------+------+------+
| Denmark  | 2020 |  -10 |
| Berlin   | 2020 |   -1 |
| Paris    | 2020 |   -1 |
| China    | 2020 |    0 |
| Denmark  | 2020 |    0 |
| Paris    | 2020 |    5 |
| Denmark  | 2020 |   10 |
| Paris    | 2020 |   10 |
| Berlin   | 2020 |   15 |
| China    | 2020 |   15 |
+----------+------+------+

The result of averaging these values can be requested with or without the "ALL" clause:

SELECT AVG(ALL temp), AVG(temp)
 FROM temperatures
 WHERE year = 2020;

+---------------+-----------+
| AVG(ALL temp) | AVG(temp) |
+---------------+-----------+
|        4.3000 |    4.3000 |
+---------------+-----------+

DISTINCT Clause

Using AVG() with the DISTINCT clause ignores duplicate values from the list, so each value is counted and summed only once when computing the average. In this example, all distinct numeric temperature values are used to compute the average:

SELECT AVG(DISTINCT temp), AVG(temp)
 FROM temperatures
 WHERE year = 2020;

+--------------------+-----------+
| AVG(DISTINCT temp) | AVG(temp) |
+--------------------+-----------+
|             3.1667 |    4.3000 |
+--------------------+-----------+

With GROUP BY

Using AVG() with GROUP BY creates a separate average for each group. In this example, GROUP BY location creates a separate average for each unique location:

SELECT location, AVG(temp)
 FROM temperatures
 GROUP BY location
 ORDER BY location;

+----------+-----------+
| location | AVG(temp) |
+----------+-----------+
| Berlin   |    1.3333 |
| China    |    7.5000 |
| Denmark  |   -0.2500 |
| Paris    |    8.5000 |
+----------+-----------+

With NULL Values

When AVG() acts on NULL values, those values are not included when computing the average. In this example, some rows contain NULL values:

SELECT *
 FROM temperatures
 WHERE year = 1920
 ORDER BY temp;

+----------+------+------+
| location | year | temp |
+----------+------+------+
| China    | 1920 | NULL |
| Berlin   | 1920 |  -10 |
| Denmark  | 1920 |   -1 |
| Paris    | 1920 |   20 |
+----------+------+------+

SELECT AVG(temp)
 FROM temperatures
 WHERE year = 1920;

+-----------+
| AVG(temp) |
+-----------+
|    3.0000 |
+-----------+

With Only NULL Values

When AVG() is acting only on NULL values, the return value is NULL:

SELECT AVG(temp)
 FROM temperatures
 WHERE year=1820;

+-----------+
| AVG(temp) |
+-----------+
|      NULL |
+-----------+

Precision and Scale

As detailed in ISO 9075:2016, the data type produced as the result of AVG() is implementation-defined. The result of AVG() will always have at least as much precision (INTEGER, DECIMAL, FLOAT, DOUBLE) and scale (INTEGER, DECIMAL) as the source values.

This example demonstrates precision and scale of AVG() acting on a DECIMAL(8,6):

CREATE TABLE avg_decimal (
  item VARCHAR(20),
  count DECIMAL(8,6)
);

INSERT INTO avg_decimal VALUES
  ('Foo', 1.123456),
  ('Bar', 3.141592),
  ('Baz', 9.999999);

SELECT AVG(count)
 FROM avg_decimal;

+--------------+
| AVG(count)   |
+--------------+
| 4.7550156667 |
+--------------+

This example demonstrates precision and scale of AVG() acting on a DECIMAL(11,9), an increase in precision from our prior input which results in increased output precision:

ALTER TABLE avg_decimal MODIFY count DECIMAL(11,9);

SELECT AVG(count)
 FROM avg_decimal;

+-----------------+
| AVG(count)      |
+-----------------+
| 4.7550156666667 |
+-----------------+