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