Calculates the average of an expression.
Syntax
AVG(<value>) [FILTER ([WHERE] <condition>)]
Parameters
| Parameter | Description | Supported input types |
|---|
<value> | The expression used to calculate the average | Any numeric type |
<condition> | An optional boolean expression to filter rows used in aggregation | BOOL |
AVG() aggregate function ignores rows with NULL values. For example, an AVG from 3 rows containing 1, 2, and NULL returns 1.5 because the NULL row is not counted. To calculate an average that includes NULL, use SUM(COLUMN)/COUNT(*).
Return Types
NUMERIC if the input is type INTEGER, BIGINT or NUMERICDOUBLE PRECISION if the input is type REAL or DOUBLE PRECISION
Precision and Determinism
Applying AVG to REAL and DOUBLE PRECISION is subject to floating point arithmetic accuracy limitations and its resulting error.
This error may add up when aggregating multiple values.
The order of operations while computing the aggregate is non-deterministic.
This can lead to varying total floating point error when running a query multiple times.
If this is not acceptable for your use-case, aggregate on NUMERIC data instead.
Example
The levels table contains the following values:
SELECT level, maxpoints FROM levels ORDER BY level;
| level int null | maxpoints int null |
|---|
| 1 | 20 |
| 2 | 30 |
| 3 | 40 |
| 4 | 100 |
| 5 | 150 |
| 6 | 80 |
| 7 | 70 |
| 8 | 100 |
| 9 | 250 |
| 10 | 500 |
Rows: 10Execution time: 6.00ms
maxpoints across all levels in the levels table:
SELECT AVG(maxpoints) AS averagemaxpoints FROM levels;
| averagemaxpoints double null |
|---|
| 134 |
Rows: 1Execution time: 8.82ms
