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

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
```
```------------------------ -----------
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
mod_cook                     7,500.00       24278
popular_comp                 7,500.00       12875
psychology                   4,255.00        9939

```

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 , Adaptive Server returns the result as an int value. When you average (signed or unsigned) bigint data, Adaptive 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.