SQL Server SUM() Function

SUM() adds numeric expression values and ignores NULLs. Its return type depends on the expression type. See Microsoft’s SUM() reference.

Syntax

SUM([ ALL | DISTINCT ] expression)

For a running or partitioned total, use the windowed form:

SUM([ ALL ] expression) OVER ([ partition_by_clause ] [ order_by_clause ])

ALL is the default and includes duplicate values; DISTINCT sums only unique expression values. SUM() accepts exact or approximate numeric expressions, but not bit.

Return type and overflow

Input type	Return type
`tinyint`, `smallint`, `int`	`int`
`bigint`	`bigint`
`decimal(p, s)`, `numeric(p, s)`	`decimal(38, s)`
`money`, `smallmoney`	`money`
`float`, `real`	`float`

SUM(int_expression) returns int and can overflow even when every input row fits in int. Cast the expression before the aggregate to widen the sum, rather than casting the result afterward. For example, use SUM(CONVERT(bigint, amount)) when bigint provides enough range. See SQL Server Error 8115: Arithmetic Overflow.

Use Cases

The SUM() function is commonly used to calculate the total of a set of data. For example, it can be used to calculate the total salary of all employees in a department.

When the select list also includes raw columns, every nonaggregate expression must be included in GROUP BY; otherwise SQL Server can raise Error 8120.

Examples

Here are two examples of using the SUM() function:

Example 1

Suppose there is an orders table that contains the following fields:

order_id	customer_id	order_date	order_amount
1	101	2022-01-01	100.00
2	102	2022-01-02	200.00
3	101	2022-01-03	150.00
4	103	2022-01-04	75.00
5	102	2022-01-05	225.00

To calculate the total amount of orders, you can use the following SQL statement:

SELECT SUM(order_amount) as total_amount
FROM orders;

The result of the query is:

total_amount
750.00

Example 2

Suppose there is a sales table that contains the following fields:

sale_id	product_name	sale_date	sale_quantity	sale_price
1	Product A	2022-01-01	10	50.00
2	Product B	2022-01-02	20	75.00
3	Product C	2022-01-03	5	100.00
4	Product A	2022-01-04	15	50.00
5	Product B	2022-01-05	30	75.00

To calculate the total sales for each product, you can use the following SQL statement:

SELECT
    product_name,
    SUM(sale_quantity * sale_price) as total_sales
FROM sales
GROUP BY product_name;