MySQL SUM Aggregate Function

MySQL aggregate functions refer to a set of functions that perform calculations on a set of values and return a single value. Aggregate functions include the maximum and minimum value, average, standard deviation, count, sum, etc.

What is Sum? How Does It Work?

The sum MySQL function does exactly as the name indicates: it returns the sum of a set of values. The sum function ignores NULL values when encountered in a set.

If used in the SELECT clause where no row is returned, the resulting value from the sum function is a NULL and not a zero.

Basic Usage

The general syntax of the MySQL sum function is as shown below:

SUM (expression)

The expression can be a single column or multiple columns separated by commas.

Example use case

Let us illustrate how the function works by using a real-world database. For this example, we shall use messy real-world data and import it into MySQL.

The data provided is in the form of CSV values.

You can download the data from the resource:

Kaggle: Your Home for Data Science

Kaggle is the world’s largest data science community with powerful tools and resources to help you achieve your data science goals.

Once you have downloaded the data, you can import it to MySQL and start using it.

To verify that you have the correct data, compare your data from the output shown from the query below:

SELECT * FROM kepler LIMIT 5;

We can use one of the columns from the database to illustrate how the sum functions.

Consider the query below that gets the sum of all the values in the koi_impact column.

SELECT SUM(koi_impact) FROM keplerdata;

This will add all the values in the table and return their sum as shown in the below output.

+------------------+
| SUM(koi_impact) |
+------------------+
| 4757.37099999998 |
+------------------+
1 row in set (0.02 sec)

Sum distinct values

If you wish to get the sum of all the unique values in a set, you can use the DISTINCT keyword as shown in the query below:

SELECT SUM(DISTINCT koi_impact) FROM keplerdata;

The output result is:

+--————————+
| SUM(DISTINCT koi_impact) |
+--————————+
| 1188.773999999995 |
+--------------------------+
1 row in set (0.02 sec)

This returns a different value than when the DISTINCT keywords are not used as only unique values are added.

Sum Function with Expression

We can also apply an expression using the WHERE clause to get only specific values. For example, consider the query below, which only adds value greater than 1 from the koi_impact table.

SELECT SUM(koi_impact) FROM keplerdata WHERE koi_impact > 1;

The result is as shown:

 +--------------------+
| SUM(koi_impact) |
+--------------------+
| 1642.4870000000005 |
+--------------------+
1 row in set (0.01 sec)

Sum with Filter

We can also add filters to the sum operations instead of adding all values, which may take a lot of time. For example, we can add the sum of all the values where the product of koi_impact and koi_impact_err1 is greater than 10.

SELECT kepid, SUM(koi_impact) FROM keplerdata GROUP BY kepid HAVING SUM(koi_impact * koi_impact_err1) > 10 ORDER BY kepid LIMIT 5;

The above query will give an output as shown:

+———+--—————+
| kepid | SUM(koi_impact) |
+---------+-----------------+
| 1575690 | 1.198 |
| 1722276 | 0.919 |
| 1996679 | 1.274 |
| 2010191 | 1.121 |
| 2021440 | 1.271 |
+---------+-----------------+
5 rows in set (0.01 sec)

Conclusion

In this tutorial, we discussed the sum function in MySQL and how to use it to get the sum of all the values in a set and filter information using the sum function.