AVG( )
Go Up to Statement and Function Reference (Language Reference Guide)
Calculates the average of numeric values in a specified column or expression. Available in gpre
, DSQL, and isql
.
AVG ([ALL] value | DISTINCT value)
Argument | Description |
---|---|
|
Returns the average of all values. |
|
Eliminates duplicate values before calculating the average. |
<value> |
A column or expression that evaluates to a numeric data type. |
Description: AVG()
is an aggregate function that returns the average of the values in a specified column or expression. Only numeric data types are allowed as input to AVG()
.
If a field value involved in a calculation is NULL
or unknown, it is automatically excluded from the calculation. Automatic exclusion prevents averages from being skewed by meaningless data.
AVG()
computes its value over a range of selected rows. If the number of rows returned by a SELECT
is zero, AVG()
returns a NULL
value.
Examples: The following embedded SQL statement returns the average of all rows in a table:
EXEC SQL
SELECT AVG (BUDGET) FROM DEPARTMENT INTO :avg_budget;
The next embedded SQL statement demonstrates the use of SUM()
, AVG()
, MIN()
, and MAX()
over a subset of rows in a table:
EXEC SQL
SELECT SUM (BUDGET), AVG (BUDGET), MIN (BUDGET), MAX (BUDGET)
FROM DEPARTMENT
WHERE HEAD_DEPT = :head_dept
INTO :tot_budget, :avg_budget, :min_budget, :max_budget;