avg

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.

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

avg, an aggregate function, finds the average of the values in a column. avg can only be used on numeric (integer, floating point, or money) datatypes. Null values are ignored in calculating averages.
When you average (signed or unsigned) int, smallint, tinyint data, the SAP ASE server returns the result as an int value. When you average (signed or unsigned) bigint data, the SAP ASE server returns the result as a bigint value. To avoid overflow errors in DB-Library programs, declare variables used for resultrs appropriately.
You cannot use avg with the binary datatypes.
Since the average value is only defined on numeric datatypes, using avg Unicode expressions generates an error.

Standards

ANSI SQL – Compliance level: Transact-SQL extension.

Permissions

Any user can execute avg.