Neoview SQL Reference Manual (R2.2)

AVG Function
AVG is an aggregate function that returns the average of a set of numbers.
AVG ([ALL | DISTINCT] expression)
ALL | DISTINCT
specifies whether duplicate values are included in the computation of the AVG of the
expression. The default option is ALL, which causes duplicate values to be included. If
you specify DISTINCT, duplicate values are eliminated before the AVG function is applied.
expression
specifies a numeric or interval value expression that determines the values to average.
The expression cannot contain an aggregate function or a subquery. The DISTINCT clause
specifies that the AVG function operates on distinct values from the one-column table derived
from the evaluation of expression.
See “Numeric Value Expressions” (page 217) and “Interval Value Expressions” (page 213).
Considerations for AVG
Data Type of the Result
The data type of the result depends on the data type of the argument. If the argument is an exact
numeric type, the result is LARGEINT. If the argument is an approximate numeric type, the
result is DOUBLE PRECISION. If the argument is INTERVAL data type, the result is INTERVAL
with the same precision as the argument.
The scale of the result is the same as the scale of the argument. If the argument has no scale, the
result is truncated.
Operands of the Expression
The expression includes columns from the rows of the SELECT result table but cannot include
an aggregate function. These expressions are valid:
AVG (SALARY)
AVG (SALARY * 1.1)
AVG (PARTCOST * QTY_ORDERED)
Nulls
All nulls are eliminated before the function is applied to the set of values. If the result table is
empty, AVG returns NULL.
Examples of AVG
• Return the average value of the SALARY column:
SELECT AVG (salary)
FROM persnl.employee;
(EXPR)
---------------------
49441.52
--- 1 row(s) selected.
• Return the average value of the set of unique SALARY values:
SELECT AVG(DISTINCT salary) AS Avg_Distinct_Salary
FROM persnl.employee;
AVG_DISTINCT_SALARY
---------------------
53609.89
AVG Function 293