SUM (T-SQL)

Returns the sum of all the values, or only the DISTINCT values, in the expression. SUM can be used with numeric columns only. Null values are ignored.

Syntax

SUM([ALL | DISTINCT] expression)

Arguments

ALL

Applies the aggregate function to all values. ALL is the default.

DISTINCT

Specifies that SUM return the sum of unique values.

expression

Is a constant, column, or function, and any combination of arithmetic, bitwise, and string operators. expression is an expression of the exact numeric or approximate numeric data type category, except for the bit data type. Aggregate functions and subqueries are not permitted.

Return Types

Returns the summation of all expression values in the most precise expression data type.

Important Distinct aggregates, for example AVG(DISTINCT column_name), COUNT(DISTINCT column_name), MAX(DISTINCT column_name), MIN(DISTINCT column_name), and SUM(DISTINCT column_name), are not supported when using CUBE or ROLLUP. If used, Microsoft® SQL Server™ returns an error message and cancels the query.

Examples

A. Use SUM for aggregates and row aggregates

These examples show the differences between aggregate functions and row aggregate functions. The first shows aggregate functions giving summary data only, and the second shows row aggregate functions giving detail and summary data.

USE pubs

GO

-- Aggregate functions

SELECT type, SUM(price), SUM(advance)

FROM titles

WHERE type LIKE '%cook'

GROUP BY type

ORDER BY type

GO

Here is the result set:

type                                                              

------------ -------------------------- --------------------------

mod_cook     22.98                      15,000.00                 

trad_cook    47.89                      19,000.00                 

(2 row(s) affected)

USE pubs

GO

-- Row aggregates

SELECT type, price, advance

FROM titles

WHERE type LIKE '%cook'

ORDER BY type

COMPUTE SUM(price), SUM(advance) BY type

Here is the result set:

type         price                      advance                   

------------ -------------------------- --------------------------

mod_cook     19.99                      0.00                      

mod_cook     2.99                       15,000.00                 

             sum

             ==========================

             22.98                     

                                        sum

                                        ==========================

                                        15,000.00                 

type         price                      advance                   

------------ -------------------------- --------------------------

trad_cook    20.95                      7,000.00                  

trad_cook    11.95                      4,000.00                  

trad_cook    14.99                      8,000.00                  

             sum

             ==========================

             47.89                     

                                        sum

                                        ==========================

                                        19,000.00                 

(7 row(s) affected)

B. Calculate group totals with more than one column

This example calculates the sum of the prices and advances for each type of book.

USE pubs

GO

SELECT type, SUM(price), SUM(advance)

FROM titles

GROUP BY type

ORDER BY type

GO

Here is the result set:

type                                                              

------------ -------------------------- --------------------------

business     54.92                      25,125.00                 

mod_cook     22.98                      15,000.00                 

popular_comp 42.95                      15,000.00                 

psychology   67.52                      21,275.00                 

trad_cook    47.89                      19,000.00                 

UNDECIDED    (null)                     (null)                    

(6 row(s) affected)

See Also

Aggregate Functions