15.00 - AVG - Teradata Database

Teradata Database SQL Functions, Operators, Expressions, and Predicates

Product
Teradata Database
Release Number
15.00
Content Type
Programming Reference
Publication ID
B035-1145-015K
Language
English (United States)
Last Update
2018-09-24

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:

  • Numeric
  • Character
  • DATE
  • Interval
  • 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.