avg

Description

Calculates the numeric average of all (distinct) values.

Syntax

avg([all | distinct] expression)

Parameters

all

applies avg to all values. all is the default.

distinct

eliminates duplicate values before avg is applied. distinct is optional.

expression

is a column name, constant, function, any combination of column names, constants, and functions connected by arithmetic or bitwise operators, or a subquery. With aggregates, an expression is usually a column name. For more information, see “Expressions”.

Examples

Example 1

Calculates the average advance and the sum of total sales for all business books. Each of these aggregate functions produces a single summary value for all of the retrieved rows:

select avg(advance), sum(total_sales) 
from titles 
where type = "business"
------------------------ ----------- 
                6,281.25       30788

Example 2

Used with a group by clause, the aggregate functions produce single values for each group, rather than for the entire table. This statement produces summary values for each type of book:

select type, avg(advance), sum(total_sales) 
from titles 
group by type
type                                              
 ------------ ------------------------ ----------- 
 UNDECIDED                        NULL        NULL 
 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 

Example 3

Groups the titles table by publishers and includes only those groups of publishers who have paid more than $25,000 in total advances and whose books average more than $15 in price:

select pub_id, sum(advance), avg(price) 
from titles 
group by pub_id 
having sum(advance) > $25000 and avg(price) > $15
 pub_id                                       
 ------ -------------------- -------------------- 
 0877              41,000.00                15.41 
 1389              30,000.00                18.98 

Usage

Standards

ANSI SQL – Compliance level: Transact-SQL extension.

Permissions

Any user can execute avg.

See also

Documentation Transact-SQL Users Guide

Functions max, min