Article
10/04/2012

SUM (Transact-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. May be followed by the OVER Clause (Transact-SQL).

Topic link icon Transact-SQL Syntax Conventions

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. For more information, see Expressions (Transact-SQL).

Return Types

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

Expression result	Return type
tinyint	int
smallint	int
int	int
bigint	bigint
decimal category (p, s)	decimal(38, s)
money and smallmoney category	money
float and real category	float

Examples

A. Using SUM for aggregates and row aggregates

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

USE AdventureWorks2008R2;
GO
SELECT Color, SUM(ListPrice), SUM(StandardCost)
FROM Production.Product
WHERE Color IS NOT NULL 
    AND ListPrice != 0.00 
    AND Name LIKE 'Mountain%'
GROUP BY Color
ORDER BY Color;
GO

Here is the result set.

Color

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

Black 27404.84 15214.9616

Silver 26462.84 14665.6792

White 19.00 6.7926

(3 row(s) affected)

USE AdventureWorks2008R2;
GO
SELECT Color, ListPrice, StandardCost
FROM Production.Product
WHERE Color IS NOT NULL 
    AND ListPrice != 0.00 
    AND Name LIKE 'Mountain%'
ORDER BY Color
COMPUTE SUM(ListPrice), SUM(StandardCost) BY Color;
GO

Here is the result set.

Color ListPrice StandardCost

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

Black 2294.99 1251.9813

Black 1079.99 598.4354

Black 3374.99 1898.0944

Black 539.99 294.5797

sum sum

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

27404.84 15214.9616

Color ListPrice StandardCost

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

Silver 2319.99 1265.6195

Silver 3399.99 1912.1544

Silver 769.49 419.7784

Silver 564.99 308.2179

sum sum

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

26462.84 14665.6792

Color ListPrice StandardCost

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

White 9.50 3.3963

sum sum

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

19.00 6.7926

(37 row(s) affected)

B. Calculating group totals with more than one column

The following example calculates the sum of the ListPrice and StandardCost for each color listed in the Product table.

USE AdventureWorks2008R2;
GO
SELECT Color, SUM(ListPrice), SUM(StandardCost)
FROM Production.Product
GROUP BY Color
ORDER BY Color;
GO

Here is the result set.

Color

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

NULL 4182.32 2238.4755

Black 67436.26 38636.5002

Blue 24015.66 14746.1464

Grey 125.00 51.5625

Multi 478.92 272.2542

Red 53274.10 32610.7661

Silver 36563.13 20060.0483

Silver/Black 448.13 198.97

White 36.98 13.5172

Yellow 34527.29 21507.6521

(10 row(s) affected)

SUM (Transact-SQL)

Syntax

Arguments

Return Types

Examples

A. Using SUM for aggregates and row aggregates

B. Calculating group totals with more than one column

See Also

Reference

Additional resources