Home / Documentation / SQL / Development / Functions / Aggregate / SUM

SUM (Transact-SQL)

Synopsis

SUM ( [ ALL | DISTINCT ] expression )

Returns the sum of all the values in the specified expression.

Arguments

ArgumentDescription
ALL | DISTINCTSpecifies whether to consider all values (default) or only distinct values.
expressionA numeric expression of any valid numeric data type.

Return Types

The return type is the same as the data type of the expression, except for int, smallint, and tinyint, which return bigint. If the expression is money or smallmoney, the return type is money. If the expression is a decimal or numeric type, the return type has a precision and scale of the same precision and scale of the expression.

Examples

Basic SUM

SELECT SUM(SalesAmount) AS TotalSales
FROM Sales.SalesOrderHeader;

SUM with DISTINCT

SELECT SUM(DISTINCT UnitPrice) AS DistinctTotal
FROM Production.Product;

Using SUM with GROUP BY

SELECT CustomerID, SUM(TotalDue) AS TotalSpent
FROM Sales.SalesOrderHeader
GROUP BY CustomerID
ORDER BY TotalSpent DESC;

Remarks

  • NULL values are ignored in the calculation.
  • SUM cannot be used with the OVER() clause without an ORDER BY.
  • When used with GROUP BY, SUM returns a result for each group.

Related Topics