SUM (Transact‑SQL)

Returns the sum of all the values in a column. It ignores NULL values.

Syntax

SUM ( [ ALL | DISTINCT ] expression )

Parameters

Parameter	Description
`ALL`	All values are included in the sum (default).
`DISTINCT`	Only distinct values are summed.
`expression`	A numeric expression to be summed. Can be a column name, arithmetic expression, or subquery.

Return Types

The return type is determined by the data type of the expression. For integer inputs, the result is the same integer type, unless overflow occurs, in which case it returns bigint.

Remarks

The SUM function ignores NULL values.
When DISTINCT is used, duplicate values are removed before summation.
For very large result sets, consider using BIGINT to avoid overflow.

Examples

Basic usage

SELECT SUM(SalesAmount) AS TotalSales
FROM Sales.SalesOrderHeader
WHERE OrderDate > '2023-01-01';

SUM with DISTINCT

SELECT SUM(DISTINCT Quantity) AS DistinctQuantitySum
FROM Production.BillOfMaterials
WHERE ComponentID = 100;

Using SUM over a derived table

SELECT DepartmentID,
       SUM(SubTotal) AS DeptTotal
FROM (SELECT DepartmentID, Salary AS SubTotal FROM HumanResources.Employee) AS DeptSal
GROUP BY DepartmentID;

MSDN Documentation