SQL/MP Reference Manual

HP NonStop SQL/MP Reference Manual—523352-013
A-77
Considerations—AVG
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

AVG is evaluated after eliminating all null values from the aggregate set. If the
result set is empty, AVG returns a null.

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 SQL/MP programming manual for your host language.)
Examples—AVG

This SELECT statement returns the average salary from the SALARY column of
the PERSNL.EMPLOYEE table:
>> SELECT AVG (SALARY) FROM PERSNL.EMPLOYEE;
(EXPR)
---------------------
48784.65
--- 1 row(s) selected.

This SELECT statement returns the average unique salary from the SALARY
column of the PERSNL.EMPLOYEE table:
>> SELECT AVG (DISTINCT SALARY) FROM PERSNL.EMPLOYEE;
(EXPR)
---------------------
52664.21
--- 1 row(s) selected.