In SQL Server, the SUM() aggregate function returns the sum of a given expression.

It can also be used to return the sum of all distinct (unique) values in the expression.

SUM() only works on numeric columns. Null values are ignored.

Syntax

The syntax goes like this:

SUM ( [ ALL | DISTINCT ] expression ) 

It can also be used with an OVER clause:

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

Example

Suppose we have a table called Products with the following data:

SELECT
    VendorId,
    ProductName,
    ProductPrice
FROM Products;

Result:

+------------+---------------------------------+----------------+
| VendorId   | ProductName                     | ProductPrice   |
|------------+---------------------------------+----------------|
| 1001       | Left handed screwdriver         | 25.99          |
| 1001       | Long Weight (blue)              | 14.75          |
| 1001       | Long Weight (green)             | 11.99          |
| 1002       | Sledge Hammer                   | 33.49          |
| 1003       | Chainsaw                        | 245.00         |
| 1003       | Straw Dog Box                   | NULL           |
| 1004       | Bottomless Coffee Mugs (4 Pack) | 9.99           |
| 1001       | Right handed screwdriver        | 25.99          |
+------------+---------------------------------+----------------+

We can use the following query to get the sum of all prices.

SELECT SUM(ProductPrice)
FROM Products;

Result:

367.20

Here, price information is stored in the ProductPrice column, and so we pass that as an argument to the SUM() function, which then calculates the sum and returns the result.

Null Values

The SUM() function ignores null values when performing its calculation.

If the column contains null values, you may see a warning indicating that null values were eliminated.

For example, here’s the warning that I got when running the above example:

SELECT SUM(ProductPrice)
FROM Products;

Result:

+--------------------+
| (No column name)   |
|--------------------|
| 367.20             |
+--------------------+
Warning: Null value is eliminated by an aggregate or other SET operation.

Filtered Results

The SUM() function operates on the rows returned by the query. So if you filter the results, the result of SUM() will reflect that.

SELECT SUM(ProductPrice)
FROM Products
WHERE VendorId = 1001;

Result:

78.72

In this case, 78.72 is the sum of all the products offered by the specified vendor.

The DISTINCT Keyword

You can use the DISTINCT keyword with SUM() to calculate only distinct values. That is, if there are any duplicate values, they are treated as one value.

Example:

SELECT 
    SUM(ALL ProductPrice) AS "All",
    SUM(DISTINCT ProductPrice) AS "Distinct"
FROM Products;

Result:

+--------+------------+
| All    | Distinct   |
|--------+------------|
| 367.20 | 341.21     |
+--------+------------+
Warning: Null value is eliminated by an aggregate or other SET operation.

Our table contains two items with the same price (the left handed screwdriver and right handed screwdriver are both priced at 25.99). The DISTINCT keyword results in both of those values being treated as one.

Window Functions

The SUM() function can be used with an OVER clause to create a window function. See SQL SUM() for Beginners for an example.