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 Syntax Conventions
- 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).
Returns the summation of all expression values in the most precise expression data type.
| Expression result | Return type |
|---|---|
|
integer category |
int |
|
decimal category (p, s) |
decimal(38, s) |
|
money and smallmoney category |
money |
|
float and real category |
float |
| 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 CUBE or ROLLUP are used. If used, the SQL Server 2005 Database Engine returns an error message and cancels the query. |
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 AdventureWorks; 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 AdventureWorks; 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 2294.99 1251.9813 Black 2294.99 1251.9813 Black 1079.99 598.4354 Black 1079.99 598.4354 Black 1079.99 598.4354 Black 1079.99 598.4354 Black 3374.99 1898.0944 Black 3374.99 1898.0944 Black 3374.99 1898.0944 Black 3374.99 1898.0944 Black 539.99 294.5797 Black 539.99 294.5797 Black 539.99 294.5797 Black 539.99 294.5797 Black 539.99 294.5797 sum sum --------------------- --------------------- 27404.84 15214.9616 Color ListPrice StandardCost --------------- --------------------- --------------------- Silver 2319.99 1265.6195 Silver 2319.99 1265.6195 Silver 2319.99 1265.6195 Silver 3399.99 1912.1544 Silver 3399.99 1912.1544 Silver 3399.99 1912.1544 Silver 3399.99 1912.1544 Silver 769.49 419.7784 Silver 769.49 419.7784 Silver 769.49 419.7784 Silver 769.49 419.7784 Silver 564.99 308.2179 Silver 564.99 308.2179 Silver 564.99 308.2179 Silver 564.99 308.2179 Silver 564.99 308.2179 sum sum --------------------- --------------------- 26462.84 14665.6792 Color ListPrice StandardCost --------------- --------------------- --------------------- White 9.50 3.3963 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 AdventureWorks; 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)
Reference
Aggregate Functions (Transact-SQL)OVER Clause (Transact-SQL)
