AVG

AVG returns the average of all the value expressions evaluated for each row in the aggregation. When used without the OVER clause, AVG is considered an aggregate function. When used with the OVER clause, it is an analytic function. (For exponential averaging, see exp_avg.)

Syntax

AVG ( [DISTINCT | ALL] <number-expression> ) [ OVER <window-specification> ]

<number-expression> can be any any numeric expression.

If DISTINCT is specified, only rows that match the <number expression> and have unique values qualify. If ALL is specified, all rows qualify. If neither DISTINCT nor ALL is specified, the behavior defaults to ALL.

When used as an analytic function, AVG will return null if the window being evaluated contains no rows, or if all rows contain null values. This will also be the result in the case of a PARTITION BY for which the partition within the window matching the input row contains no rows or all rows are null.

Otherwise AVG ignores null values. AVG of 1, 2, 3 is 2. AVG of 1,null, 2, null, 3, null is also 2 - the null values aren’t counted as part of the total or in the count of rows. So AVG(x) is the same as SUM(x) / COUNT(x).

Example

This example shows the difference between AVG(ALL pct_free), which is calculated as (71 * 10 + 1 * 0)/72 = 9.86, and AVG(DISTINCT pct_free), which is calculated as (10 + 0)/2 = 5.

0: jdbc:farrago:rmi://localhost> select pct_free, count(*) from test1 group by pct_free;
+-----------+---------+
PCT_FREE   EXPR$1  
+-----------+---------+
10.0       71      
0.0        1      
+-----------+---------+
2 rows selected (0.672 seconds)
0: jdbc:farrago:rmi://localhost> select avg(all pct_free) as avg_all from test1;
+-------------------+
     AVG_ALL      
+-------------------+
9.86111111111111  
+-------------------+
1 row selected (0.438 seconds)
0: jdbc:farrago:rmi://localhost> select avg(distinct pct_free) as avg_distinct from test1;
+---------------+
AVG_DISTINCT  
+---------------+
5.0          
+---------------+
1 row selected (0.516 seconds)

Limitations

AVG is only supported on numeric types.

Note: SQLstream does not support AVG applied to interval types. This is a departure from the SQL standard.