AVG (T-SQL)

Returns the average of the values in a group. Null values are ignored.

Syntax

AVG([ALL | DISTINCT] expression)

Arguments
ALL
Applies the aggregate function to all values. ALL is the default.
DISTINCT
Specifies that AVG be performed only on each unique instance of a value, regardless of how many times the value occurs.
expression
Is an expression of the exact numeric or approximate numeric data type category, except for the bit data type. Aggregate functions and subqueries are not permitted.
Return Types

The return type is determined by the type of the evaluated result of expression.

Expression result Return type
integer category int
decimal category (p, s) decimal(38, s) divided by decimal(10, 0)
money and smallmoney category money
float and real category float

Important Distinct aggregates, for example, AVG(DISTINCT column_name), COUNT(DISTINCT column_name), MAX(DISTINCT column_name), MIN(DISTINCT column_name), and SUM(DISTINCT column_name), are not supported when using CUBE or ROLLUP. If used, Microsoft® SQL Server™ returns an error message and cancels the query.

Examples
A. Use SUM and AVG functions for calculations

    This example calculates the average advance and the sum of year-to-date sales for all business books. Each of these aggregate functions produces a single summary value for all of the retrieved rows.

    USE pubs

      

    SELECT AVG(advance), SUM(ytd_sales)

    FROM titles

    WHERE type = 'business'

      

    Here is the result set:

    -------------------------- -----------

    6,281.25                   30788      

      

    (1 row(s) affected)

      

    B. Use SUM and AVG functions with a GROUP BY clause

      When used with a GROUP BY clause, each aggregate function produces a single value for each group, rather than for the whole table. This example produces summary values for each type of book that include the average advance for each type of book and the sum of year-to-date sales for each type of book.

      USE pubs

        

      SELECT type, AVG(advance), SUM(ytd_sales)

      FROM titles

      GROUP BY type

      ORDER BY type

        

      Here is the result set:

      type                                               

      ------------ -------------------------- -----------

      business     6,281.25                   30788      

      mod_cook     7,500.00                   24278      

      popular_comp 7,500.00                   12875      

      psychology   4,255.00                   9939       

      trad_cook    6,333.33                   19566      

      UNDECIDED    NULL                       NULL     

        

      (6 row(s) affected)

        

      C. Use AVG with DISTINCT

        This statement returns the average of the distinct prices of business books.

        USE pubs

        SELECT AVG(DISTINCT price)

        FROM titles

        WHERE type = 'business'

        Here is the result set:

        --------------------------

        11.64                     

          

        (1 row(s) affected)

          

        D. Use AVG without DISTINCT

          Without DISTINCT, the AVG function finds the average price of all business titles in the titles table.

          USE pubs

            

          SELECT AVG(price)

          FROM titles

          WHERE type = 'business'

            

          Here is the result set:

          --------------------------

          13.73                     

            

          (1 row(s) affected)

            

          See Also

          Aggregate Functions


          (c) 1988-98 Microsoft Corporation. All Rights Reserved.