An aggregate function that returns the average of the values of the specified
column.
Synopsis
AVG([ALL | DISTINCT] expression)
The
AVG aggregate function returns the average of the values
of
expression. Commonly,
expression is the name
of a field, (or an expression containing one or more field names) in the multiple
rows returned by a query.
AVG can be used in a
SELECT query
or subquery that references either a table or a view.
AVG can
appear in a
SELECT list or
HAVING clause alongside
ordinary field values.
AVG cannot be used in a
WHERE clause.
AVG cannot
be used in the
ON clause of a
JOIN, unless the
SELECT is
a subquery.
The DISTINCT keyword with
AVG performs the aggregate operation
on only those fields having distinct (unique) values. The ALL keyword is optional.
The default is to average all values.
AVG returns a double-precision floating point number. By
default, aggregate functions use Logical (internal) data values, rather than Display
values.
AVG is normally applied to a field or expression that has
a numeric value. Because no type checking is performed, it is possible (though rarely
meaningful) to invoke it for non-numeric fields.
AVG evaluates
non-numeric values, including the empty string (''), as zero (0).
NULL values in data fields are ignored when deriving an
AVG aggregate
function value. If no rows are returned by the query, or the data field value for
all rows returned is NULL,
AVG returns NULL.
The following query lists the average salary for all employees in the Sample.Employee
database. Because all rows returned by the query would have identical values for this
average, this query only returns a single row, consisting of the average salary. For
display purposes, this query concatenates a dollar sign to the value (using the ||
operator), and uses the AS clause to label the column:
SELECT '$' || AVG(Salary) AS AverageSalary
FROM Sample.Employee
The following query lists the name, salary, and average salary for all employees
whose salary is greater than the average salary. The rows are listed in ascending
order by salary. The field value for average salary is the same for all rows returned
by the query:
SELECT Name,Salary,AVG(Salary) AS AverageSalary
FROM Sample.Employee
HAVING Salary>AVG(Salary)
ORDER BY Salary
SELECT DISTINCT AVG(NULL) AS NullVal,
AVG('') AS EmpStrVal,
AVG(' ') AS BlankVal,
AVG(0) AS ZeroVal
FROM Sample.Person
&sql(SELECT DISTINCT AVG(NULL),
AVG(''),
AVG(' ')
INTO :a,:b,:c
FROM Sample.Person)
WRITE !,"SQLCODE=",SQLCODE
WRITE !,"NullVal=",a," Length=",$LENGTH(a)
WRITE !,"StrVal=",b," Length=",$LENGTH(b)
WRITE !,"BlankVal=",c," Length=",$LENGTH(c)