In MySQL, 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 an expression.

Syntax

The syntax goes like this:

SUM([DISTINCT] expr) [over_clause]

Sample Data

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 | Right handed screwdriver        |        25.99 |
|     1001 | Long Weight (blue)              |        14.75 |
|     1001 | Long Weight (green)             |        11.99 |
|     1002 | Sledge Hammer                   |         NULL |
|     1003 | Chainsaw                        |       245.00 |
|     1003 | Straw Dog Box                   |        55.99 |
|     1004 | Bottomless Coffee Mugs (4 Pack) |         9.99 |
+----------+---------------------------------+--------------+

Example

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

SELECT SUM(ProductPrice)
FROM Products;

Result:

389.70

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 any NULL values. In our sample table above, the sledgehammer has got a NULL value in its ProductPrice column, but that was ignored in the calculation.

Filtered Results

The SUM() function operates on the rows returned by the query. So if you filter the results, the output 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(DISTINCT ProductPrice)
FROM Products;

Result:

363.71

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

We can use an OVER clause with the SUM() function to create a window function. See SQL SUM() for Beginners for an example.