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;