Skip to main content

AVG (SQL)

An aggregate function that returns the average of the values of the specified column.

Synopsis

AVG([ALL | DISTINCT [BY(col-list)]] expression 
  [%FOREACH(col-list)] [%AFTERHAVING])

Description

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.

AVG, like all aggregate functions, can take an optional DISTINCT clause. AVG(DISTINCT col1) averages only those col1 field values that are distinct (unique). AVG(DISTINCT BY(col2) col1) averages only those col1 field values in records where the col2 values are distinct (unique). Note however that the distinct col2 values may include a single NULL as a distinct value.

Data Values

For non-DOUBLE expression values, AVG returns a double-precision floating point number. The precision of the value returned by AVG is 18. The scale of the returned value depends upon the precision and scale of expression: the scale of the value returned by AVG is equal to 18 minus the expression precision, plus the expression scale.

For DOUBLE expression values, the scale is 0.

AVG is normally applied to a field or expression that has a numeric value, such as a number field or a date field. By default, aggregate functions use Logical (internal) data values, rather than Display values. Because no type checking is performed, it is possible (though rarely meaningful) to invoke it for nonnumeric fields; AVG evaluates nonnumeric values, including the empty string (''), as zero (0). If expression is data type VARCHAR, the return value is data type DOUBLE.

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.

Averaging a Single Value

If all of the expression values supplied to AVG are the same, the resulting average depends on the number of accessed rows in the table (the divisor). For example, if all of the rows in the table have the same value for a specific column, the average value of that column is a calculated value, which may differ slightly from the value in the individual columns.

The following example shows how a slight inequality can result from the calculation of an average. The first query does not reference table rows, so AVG calculates by dividing by 1. The second query references table rows, so AVG calculates by dividing by the number of rows in the table.

SELECT
  {fn PI} AS Pi,
  AVG({fn PI}) AS AvgPiDividedBy1
FROM Sample.Person
SELECT
  Name,
  {fn PI} AS Pi,
  AVG({fn PI}) AS AvgPiDividedByNumRows
FROM Sample.Person

Optimization

SQL optimization of an AVG calculation can use a bitslice index, if this index is defined for the field.

Changes Made During the Current Transaction

Like all aggregate functions, AVG always returns the current state of the data, including uncommitted changes, regardless of the current transaction’s isolation level. For further details, refer to SET TRANSACTION and START TRANSACTION.

Arguments

ALL

An optional argument specifying that AVG return the average of all values for expression. This is the default if no keyword is specified.

DISTINCT

An optional DISTINCT clause that specifies that AVG calculate the average on only the unique instances of a value. DISTINCT can specify a BY(col-list) subclause, where col-list can be a single field or a comma-separated list of fields.

expression

Any valid expression. Usually the name of a column that contains the data values to be averaged.

%FOREACH(col-list)

An optional column name or a comma-separated list of column names. See SELECT for further information on %FOREACH.

%AFTERHAVING

An optional argument that applies the condition found in the HAVING clause.

AVG returns either the NUMERIC or DOUBLE data type. If expression is data type DOUBLE, AVG returns DOUBLE; otherwise, it returns NUMERIC.

Examples

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 each state with the average salary for the employees in that state:

SELECT Home_State,'$' || AVG(Salary) AS AverageSalary
     FROM Sample.Employee
GROUP BY Home_State

The following query lists the name and salary for those employees whose salary is greater than the average salary. It also lists the average salary for all employees; this value is the same for all rows returned by the query:

SELECT Name,Salary,
       '$' || AVG(Salary) AS AverageAllSalary
FROM Sample.Employee
HAVING Salary>AVG(Salary)
ORDER BY Salary

The following query lists the name and salary for those employees whose salary is greater than the average salary. It also lists the average salary for those employees with above-average salaries; this value is the same for all rows returned by the query:

SELECT Name,Salary,
       '$' || AVG(Salary %AFTERHAVING) AS AverageHighSalary
FROM Sample.Employee
HAVING Salary>AVG(Salary)
ORDER BY Salary

The following query lists those states containing more than three employees with the average salary of that state's employees, and the average salary of that state's employees earning more than $20,000:

SELECT Home_State,
       '$' || AVG(Salary) AS AvgStateSalary,
       '$' || AVG(Salary %AFTERHAVING) AS AvgLargerSalaries
FROM Sample.Employee
GROUP BY Home_State
HAVING COUNT(*) > 3 AND Salary > 20000
ORDER BY Home_State

The following query uses several forms of the DISTINCT clause. The AVG(DISTINCT BY col-list examples may include an additional Age value in the average, because the BY clause can include a single NULL as a distinct value, if Home_City contains one or more NULLs:

SELECT AVG(Age) AS AveAge,AVG(ALL Age) AS Synonym,
       AVG(DISTINCT Age) AS AveDistAge,
       AVG(DISTINCT BY(Home_City) Age) AS AvgAgeDistCity,
       AVG(DISTINCT BY(Home_City,Home_State) Age) AS AvgAgeDistCityState
     FROM Sample.Person

The following query uses both the %FOREACH and the %AFTERHAVING keywords. It returns a row for those states containing people whose names start with “A”, “M”, or “W” (HAVING clause and GROUP BY clause). Each state row contains the following values:

  • LIST(Age %FOREACH(Home_State)): a list of the ages of all of the people in the state.

  • AVG(Age %FOREACH(Home_State)): the average age of all of the people in the state.

  • AVG(Age %AFTERHAVING): the average age of all of the people in the database that meet the HAVING clause criteria. (This number is the same for all rows.)

  • LIST(Age %FOREACH(Home_State) %AFTERHAVING): a list of the ages of all of the people in the state that meet the HAVING clause criteria.

  • AVG(Age %FOREACH(Home_State) %AFTERHAVING): the average age of all of the people in the state that meet the HAVING clause criteria.

SELECT Home_State,
       LIST(Age %FOREACH(Home_State)) AS StateAgeList,
       AVG(Age %FOREACH(Home_State)) AS StateAgeAvg,
       AVG(Age %AFTERHAVING ) AS AgeAvgHaving,
       LIST(Age %FOREACH(Home_State)%AFTERHAVING ) AS StateAgeListHaving,
       AVG(Age %FOREACH(Home_State)%AFTERHAVING ) AS StateAgeAvgHaving
FROM Sample.Person
GROUP BY Home_State
HAVING Name LIKE 'A%' OR Name LIKE 'M%' OR Name LIKE 'W%'
ORDER BY Home_State

See Also

FeedbackOpens in a new tab