AVG
Purpose
Returns the arithmetic average of all values in value_expression.
Syntax
where:
|
Syntax element … |
Specifies … |
|
ALL |
that all non-null values specified by value_expression, including duplicates, are included in the average computation for the group. |
|
DISTINCT |
that null and duplicate values specified by value_expression are eliminated from the average computation for the group. |
|
value_expression |
a literal or column expression for which an average is to be computed. The expression cannot contain any ordered analytical or aggregate functions. |
ANSI Compliance
This is ANSI SQL:2011 compliant.
AVERAGE and AVE are Teradata extensions to the ANSI standard.
Result Type and Attributes
The following table lists the default attributes for the result of AVG.
|
Attribute |
Value |
|||
|
Data Type |
REAL |
|||
|
Title |
Average(x) |
|||
|
Format |
If the operand is numeric, date, or interval, the format is the same format as x. If the operand is character, the format is the default format for FLOAT. If the operand is a UDT, the format is the format for the data type to which the UDT is implicitly cast. |
|||
For an explanation of the formatting characters in the format, see “Data Type Formats and Format Phrases” in SQL Data Types and Literals.
Support for UDTs
By default, Teradata Database performs implicit type conversion on a UDT argument that has an implicit cast that casts between the UDT and any of the following predefined types:
To define an implicit cast for a UDT, use the CREATE CAST statement and specify the AS ASSIGNMENT clause. For more information on CREATE CAST, see SQL Data Definition Language.
Implicit type conversion of UDTs for system operators and functions, including AVG, is a Teradata extension to the ANSI SQL standard. To disable this extension, set the DisableUDTImplCastForSysFuncOp field of the DBS Control Record to TRUE. For details, see Utilities: Volume 1 (A-K).
For more information on implicit type conversion of UDTs, see Chapter 13: “Data Type Conversions.”
Computation of INTEGER or DECIMAL Values
An AVG of a DECIMAL or INTEGER value may overflow if the individual values are very large or if there is a large number of values.
If this occurs, change the AVG call to include a CAST function that converts the DECIMAL or INTEGER values to REAL as shown in the following example:
AVG(CAST(value AS REAL) )
Casting the values as REAL before averaging causes a slight loss in precision.
The type of the result is REAL in either case, so the only effect of the CAST is to accept a slight loss of precision where a result might not otherwise be available at all.
If x is an integer, AVG does not display a fractional value. A fractional value may be obtained by casting the value as DECIMAL, for example the following CAST to DECIMAL.
CAST(AVG(value) AS DECIMAL(9,2))
Restrictions
The value_expression must not be a column reference to a view column that is derived from a function.
AVG is valid only for numeric data.
Nulls are not included in the result computation. For more information, see SQL Fundamentals and “Aggregates and Nulls” on page 31.
Example
This example queries the sales table for average sales by region and returns the following results.
SELECT Region, AVG(sales)
FROM sales_tbl
GROUP BY Region
ORDER BY Region;
Region Average (sales)
------ ---------------
North 21840.17
East 55061.32
Midwest 15535.73
AVG Window Function
For the AVG window function that computes a group, cumulative, or moving average, see “Window Aggregate Functions” on page 984.