AVG

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.

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 price 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