AVG

Computes the arithmetic mean of the values in the argument. Null values are ignored.

SQL syntax

AVG ([ALL | DISTINCT] Expression) [OVER ([AnalyticClause])]

Parameters

AVG has the following parameters:

Parameter	Description
Expression	Can be any numeric data type or any nonnumeric data type that can be implicitly converted to a numeric data type.
ALL	Includes duplicate rows in the argument of an aggregate function. If neither ALL nor DISTINCT is specified, ALL is assumed.
DISTINCT	Eliminates duplicate column values from the argument of an aggregate function.
OVER ([AnalyticClause])	If specified, indicates aggregate analytic function. For more information on analytic functions, see "Analytic Functions".

Description

• If AVG is computed over an empty table in which GROUP BY is not used, then AVG returns NULL.

• If AVG is computed over an empty group or an empty grouped table (GROUP BY is used), AVG returns nothing.

• AVG is evaluated as SUM/COUNT. The result data type is derived using the rule that is applied for the DIV operator.

• If you do not specify the AnalyticClause in your query, then AVG acts as an aggregate function.

• If you specify DISTINCT and the AnalyticClause, then you can only specify the QueryPartitionClause. The OrderByClause and WindowingClause are not allowed.

Examples

Calculate the average salary for employees in the HR schema. Use CAST to cast the average as the data type of the column:

Command> SELECT CAST(AVG (salary) AS NUMBER (8,2)) FROM employees;
< 6461.68 >