Chapter 2: Transact-SQL Functions

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

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.When you sum bigint data, Adaptive Server treats the result as a bigint.To avoid overflow errors in DB-Library programs, declare all variables for results of averages or sums appropriately.
You cannot use sum with the binary datatypes.
This function defines only numeric types; use with Unicode expressions generates an error.

Standards

ANSI SQL – Compliance level: Transact-SQL extension.

Permissions

Any user can execute sum.

sum