SQL/MX 2.x Reference Manual (H06.04+)

SQL/MX Functions and Expressions
HP NonStop SQL/MX Reference Manual—540440-003
9-14
AVG Function
AVG Function
AVG is an aggregate function that returns the average of a set of numbers.
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 on page 6-48 and Interval Value Expressions on
page 6-43.
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.
AVG ([ALL | DISTINCT] expression)