AVG is an aggregate function that returns arithmetic average of all values in value_expression. This is used in conjunction with GROUP BY clause.
Value_expression => is a literal or column expression for which an average is to be computed. The expression cannot contain any ordered analytical or aggregate functions.
[Type] is an optional parameter. Type could be of “ALL” or “DISTINCT”.
For ALL => all non-null values specified by value_expression including duplicates, are included in the average computation for the group.
For DISTINCT => null and duplicate values specified in the value_expression are eliminated from average computation for the group.
Result type of Teradata average:
Default data type of the result of AVG(x) is REAL. If X 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.
Teradata Average 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