The MySQL SUM() function explained

The MySQL SUM() function is an aggregate function that allows you to calculate the sum of values passed as an expression to the function.

The syntax of the function is as follows:

SELECT SUM(expression)

The expression is usually a column’s name in your table.

Let’s see the SUM() function in action. Imagine you have a table named example with two columns: quantity and price as shown below.

+----------+-------+
| quantity | price |
+----------+-------+
|        2 |   3.7 |
|        4 |   1.5 |
|        1 |     2 |
|        2 |  NULL |
+----------+-------+

You can find the sum of the quantity column values with the following SQL query:

SELECT SUM(quantity) AS sum_quantity 
  FROM example;

The SELECT statement above will return the sum of the quantity column, which is 9:

+--------------+
| sum_quantity |
+--------------+
|            9 |
+--------------+

You can also use the DISTINCT keyword to sum only the distinct values from the matching rows:

SELECT SUM(DISTINCT(quantity)) AS sum_quantity 
  FROM example;

--   +--------------+
--   | sum_quantity |
--   +--------------+
--   |            7 |
--   +--------------+

The DISTINCT keyword above causes the duplicate quantity value of 2 to be excluded from the SUM() function.

SUM function ignores NULL values

The SUM() function will return NULL when there’s no row that matches the WHERE clause condition (if you add it)

SELECT SUM(quantity) AS sum_quantity 
  FROM example
  WHERE quantity = 3;

-- returns NULL

The function also ignores NULL value, adding only rows with existing values.

Back to the example table, the price column will add only 3 rows from the 4 available rows:

SELECT SUM(price) AS sum_quantity 
  FROM example;

-- 3.7 + 1.5 + 2 = 7.2

Finally, you can also perform math operations between columns with number type as the parameter for the SUM() function:

SELECT SUM(quantity*price) AS sum_quantity 
  FROM example;

-- (2 * 3.7) + (4 * 1.5) + (1 * 2) = 15.4

--   +--------------+
--   | sum_quantity |
--   +--------------+
--   |         15.4 |
--   +--------------+

The fourth row from the table will be ignored because of the NULL value in the price column.

And that’s how the MySQL SUM function works.