SUM (Transact-SQL)

Applies to: SQL Server, Azure SQL Database, Azure Synapse Analytics, Platform for Analytics

Returns the sum of the values in an expression. SUM can only be used with columns that contain numeric data. Aggregate functions ignore NULL values.

If you want to count the number of rows, use COUNT.

To get the sum of values across a set of rows, use SUM ( expression ) [ OVER ( [ partition_by_clause ] order_by_clause ) ].

Syntax

SELECT SUM(column_name)
FROM table_name
WHERE condition;

Arguments

expression

Is an expression of the exact numeric or approximate numeric data type categories. It cannot contain subqueries or aggregate functions.

Return Types

Returns the sum of the input values. The data type of the return value depends on the data type of the input values. For example, if the input values are of type int, the sum will be of type int. If the input values are of type money, the sum will be of type money.

Remarks

• The SUM function calculates the sum of all values in a specified column.
• It can be used with the GROUP BY clause to calculate the sum for each group.
• SUM ignores NULL values. If you need to include NULL values in your calculation, you can use ISNULL or COALESCE to replace them with a default value (e.g., 0).

Examples

SELECT SUM(TotalDue) AS TotalSales
FROM SalesOrderHeader;

SELECT TerritoryID, SUM(TotalDue) AS TerritoryTotalSales
FROM SalesOrderHeader
GROUP BY TerritoryID;

SELECT SUM(COALESCE(OrderQty, 0)) AS TotalQuantitySold
FROM SalesOrderDetail
WHERE SalesOrderID IN (
    SELECT SalesOrderID
    FROM SalesOrderHeader
    WHERE YEAR(OrderDate) = 2023
);