Returns the total of the values.


sum([all | distinct] expression)



applies sum to all values. all is the default.


eliminates duplicate values before sum is applied. distinct is optional.


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”.


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



SQL92 – Compliance level: Transact-SQL extension.


Any user can execute sum.

See also

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

Functions count, max, min