# avg

### Description

Returns 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 whole 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.

• For general information about aggregate functions, see “Aggregate functions”.

• When you average integer data, Adaptive Server treats the result as an int value, even if the datatype of the column is smallint or tinyint. To avoid overflow errors in DB-Library programs, declare all variables for results of averages or sums as type int.

• You cannot use avg() with the binary datatypes.

• Since the average value is only defined on numeric datatypes, use with Unicode expressions generates an error.

### Standards

SQL92 – Compliance level: Transact-SQL extension.

### Permissions

Any user can execute avg.