AVG

AVG is a column function valid only on columns containing numeric data.

The following example includes more than one column function in the SELECT statement. For Department 10, it calculates and displays the following: 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:

       COL1        COL2             COL3        COL4              COL5
----------- ----------- ---------------- ----------- -----------------
   83463.45    19260.25 20865.8625000000    22959.20                 4

Write the AVG column function like this:

AVG(expression)

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

An arithmetic expression containing at least one column name
The DISTINCT keyword, 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.

You cannot use the AVG function on a column if the sum of the data in the column would cause an overflow condition.