Syntax
AVG ( [ DISTINCT | ALL ] Expression )
The DISTINCT qualifier eliminates duplicates.
The ALL qualifier retains duplicates. ALL is the default value if neither
ALL nor DISTINCT is specified. For example, if a column contains the values
1.0, 1.0, 1.0, 1.0, and 2.0, AVG(col) returns a smaller value than AVG(DISTINCT
col).
Only one DISTINCT aggregate expression per
SelectExpression is
allowed. For example, the following query is not valid:
SELECT AVG (DISTINCT flying_time), SUM (DISTINCT miles)
FROM Flights
The expression can contain multiple column
references or expressions, but it cannot contain another aggregate or subquery.
It must evaluate to an SQL-92 numeric data type. You can therefore call methods
that evaluate to SQL-92 data types. If an expression evaluates to NULL, the
aggregate skips that value.
The resulting data type is the same as
the expression on which it operates (it will never overflow). The following
query, for example, returns the INTEGER
1, which might not be what
you would expect:
SELECT AVG(c1)
FROM (VALUES (1), (1), (1), (1), (2)) AS myTable (c1)
CAST
the expression to another data type if you want more precision:
SELECT AVG(CAST (c1 AS DOUBLE PRECISION))
FROM (VALUES (1), (1), (1), (1), (2)) AS myTable (c1)
