NonStop SQL/MP Reference Manual

NonStop SQL/MP Reference Manual—142115
A-71
AVG Function
See TMF Transactions on page T-5 for more information.
AVG Function
AVG is a function that computes the average of a set of numbers.
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 FLOAT, REAL, or
DOUBLE PRECISION type, the result is DOUBLE PRECISION.
The scale of the result is the same as the scale of the argument. If the argument has no
scale, the result is truncated.
[ ALL ] expression
specifies a numeric or INTERVAL expression that indicates the set of values to
average.
The expression must include a value from each row of the result table (that is, at
least one column from the result table), and cannot include the COUNT, MAX,
MIN, or SUM functions, or another AVG function. For example,
AVG (SALARY)
AVG (PARTCOST * QTY_ORDERED)
ALL is an optional keyword that does not change the meaning of the clause. Unless
you use the DISTINCT clause, SQL uses all rows whether or not you specify ALL.
DISTINCT column
specifies a set of distinct column values from each row of the result table to average.
The column cannot be a column from a view that corresponds to an expression in
the view definition.
If you specify DISTINCT in more than one AVG function in the same statement, the
functions must reference the same column.
Considerations—AVG
•
Null values
AVG is evaluated after eliminating all null values from the aggregate set. If the
result set is empty, AVG returns a null.
•
Indicator required for host variables
A host variable that receives the result of AVG must have an indicator variable to
handle a possible null value. (For more information about using indicator variables,
see the NonStop SQL/MP programming manual for your host language.)
AVG { ( [ ALL ] expression ) }
{ ( DISTINCT column ) }