# sum

### Description

Returns the total of the values.

### Syntax

sum([all | distinct] *expression*)

### Parameters

**all**
applies **sum** to all values. **all** is
the default.

**distinct**
eliminates duplicate values before **sum** 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"

*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

*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

### Usage

**sum**,
an aggregate function, finds the sum of all the values in a column. **sum** can
only be used on numeric (integer, floating point, or money) datatypes.
Null values are ignored in calculating sums.

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

When
you sum 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 **sum** with the binary datatypes.

Since this function only defines numeric types,
use with **Unicode** expressions generates an error.

### Standards

**SQL92** – Compliance level: Transact-SQL
extension.

### Permissions

Any user can execute** sum**.

### See also

**Commands** compute clause, group by and having clauses, select, where clause

**Functions** count, max, min