SUM

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