avg Function (XQuery)

avg Function (XQuery)

Applies To: SQL Server 2014, SQL Server 2016 Preview

Topic Status: Some information in this topic is preview and subject to change in future releases. Preview information describes new features or changes to existing features in Microsoft SQL Server 2016 Community Technology Preview 2 (CTP2).

Returns the average of a sequence of numbers.

fn:avg($arg as xdt:anyAtomicType*) as xdt:anyAtomicType?


The sequence of atomic values whose average is computed.

All the types of the atomized values that are passed to avg() have to be a subtype of exactly one of the three built-in numeric base types or xdt:untypedAtomic. They cannot be a mixture. Values of type xdt:untypedAtomic are treated as xs:double. The result of avg() receives the base type of the passed in types, such as xs:double in the case of xdt:untypedAtomic.

If the input is statically empty empty is implied and a static error is raised.

The avg() function returns the average of the numbers computed. For example:

sum($arg) div count($arg)

If $arg is an empty sequence, the empty sequence is returned.

If an xdt:untypedAtomic value cannot be cast to xs:double, the value is disregarded in the input sequence, $arg.

In all other cases, the function returns a static error.

This topic provides XQuery examples against XML instances that are stored in various xml type columns in the AdventureWorks database.

A. Using the avg() XQuery function to find work center locations in the manufacturing process in which labor hours are greater than the average for all work center locations.

You can rewrite the query provided in min function (XQuery) to use the avg() function.

These are the limitations:

  • The avg() function maps all integers to xs:decimal.

  • The avg() function on values of type xs:duration is not supported.

  • Sequences that mix types across base type boundaries are not supported.

Community Additions

© 2015 Microsoft