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 entire 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

Standards

ANSI SQL – 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