AVG

Returns the average among all the values, or only the DISTINCT values, of the specified field-expression.

Arguments:

DISTINCT

Specifies that AVG returns the average of unique values.
DISTINCT can only be used when the query does not make use of the GROUP BY clause.

ALL

Applies the aggregate function to all values. ALL is the default.

<field_expr>

The field-expression whose values are to be averaged.
The field-expression data type must be INTEGER or REAL.

Return Type:

INTEGER or REAL, depending on the argument field-expression.

Remarks:

• NULL values are ignored by the AVG aggregate function.
• Aggregate functions are allowed as field-expressions only in the SELECT, HAVING, and ORDER BY clauses.
• The arguments of an aggregate function can not reference other aggregate functions.
• The arguments of an aggregate function can not reference the following functions:
  • SEQUENCE
  • OUT_ROW_NUMBER
• DISTINCT is allowed in aggregate functions only when there is no GROUP BY clause.

Examples:

A. AVG

The following query returns the average number of bytes for executable files in the "system32" directory, using the FS input format:

SELECT AVG(Size)
FROM C:\windows\system32\*.*
WHERE TO_LOWERCASE(EXTRACT_EXTENSION(Name)) = 'exe'

B. AVG and GROUP BY

The following query returns the average time spent by each page extension logged in the specified IIS W3C log file:

SELECT  TO_LOWERCASE(EXTRACT_EXTENSION(cs-uri-stem)) AS PageType,
        AVG(time-taken)
FROM ex031118.log
GROUP BY PageType

See also:

Aggregate Functions

Aggregating Data Within Groups

© 2004 Microsoft Corporation. All rights reserved.