How to Use the MySQL SUM() Function
In this tutorial, you'll learn how to use the MySQL SUM() function to calculate the sum of a set of values.
MySQL SUM() is one of the aggregate functions in MySQL. It allows us to calculate the sum of a set of values in MySQL.
Note that MySQL ignores NULL values.
MySQL SUM() Syntax
The syntax of the MySQL SUM() function is as follows:
The expression parameter is required and can be a column or a formula.
To use the SUM() function in the SELECT statement, you can write as follows:
But, if you want to specify conditions, you can write like this:
SUM() Function Examples
Suppose you have the following orders table that consists of 5 columns, order_number, customer_id, customer_name, amount, and status.
Here, we assume that column customer_id is a primary key column.
SUM() without Conditions
Let's say you want to calculate all the amount in the orders table, and then you can write the following MySQL query.
Since no conditions specified, MySQL sums all amounts in the amount column.
SUM() with Conditions
In this example, let's say you want to calculate the total amount ordered by the customer Iris Wilkerson. The SELECT statement will look like this:
WHERE customer_name = "Iris Wilkerson";
As you can see in the above table, three records meet the specified condition, so SUM() sums the values of 1200.80, 450.60, and 950.60.
Let's take a look at the above table. As you can see, two customers have the same name, Iris Wilkerson, but they have different customer IDs. So, in this case, SUM() sum all the amounts of these two customers.
To sum all the amounts of the unique customer, you should use the customer_id column instead, because the customer_id column is a primary key column, and can store unique values only.
Now the statement will look like this:
WHERE customer_id ="#CU_200002";
Next, let's say you want to get only the total amount of the confirmed orders, then you can write the SQL statement as follows:
WHERE status = "Confirmed";
MySQL searches for any record that status is Confirmed and then sums all values of the amount column.
In this tutorial, you've learned how to use the MySQL SUM() function to sum a set of values. You can use SUM() to sum all the records or the record that meets the specified conditions only.