Teradata Average

AVG is an aggregate function that returns arithmetic average of all values in value_expression. This is used in conjunction with GROUP BY clause.

Syntax:

AVG([Type] value_expression)

Where

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;

OUTPUT:

   Region  Average (sales)
   ------  ---------------
   North          21840.17
   East           55061.32
   Midwest        15535.73