AVG

AVG is a column function. The following example includes more than one column function in the SELECT statement. For Department 10, it calculates and displays the sum of employee salaries, the minimum, average, and maximum salary, and the number of employees (COUNT) in the department.

This query:

SELECT SUM(SALARY), MIN(SALARY), AVG(SALARY),
   MAX(SALARY), COUNT(*)
FROM Q.STAFF
WHERE DEPT = 10

Produces this report:

SUM(SALARY) MIN(SALARY)      AVG(SALARY) MAX(SALARY) COUNT(EXPRESSION)
----------- ----------- ---------------- ----------- -----------------
   83463.45    19260.25 20865.8625000000    22959.20                 4

Write a column function like this:

AVG(expression)

The parentheses are required. expression is most often a column name, but can also be:

• An arithmetic expression containing at least one column name.
• DISTINCT, followed by a column name.

A column name in a function must not refer to a long string column or a column derived from a column function (a column of a view can be derived from a function). Column functions cannot be nested within other column functions. Null values are not included in the calculation made by a column function.