How to Use the MySQL SUM() Function
In this tutorial, you will learn how to calculate the sum of a set of values using the MySQL SUM() function.
You can use SUM() in combination with the SELECT statement and other functions.
SUM() is one of the MySQL aggregate functions (MIN(), MAX(), AVG(), COUNT() etc.).
MySQL SUM() Syntax
The syntax of the MySQL SUM() function is as follows:
SUM(expression)
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:
SELECT SUM(expression)
FROM table;
You also can use SUM() in combination with the WHERE clause to get the total value in the record that matches the condition.
SELECT SUM(expression)
FROM table
WHERE where_conditions;
SUM() Function Examples
Suppose you have the table named orders that consists of five columns: order_number, customer_id, customer_name, amount, and status.
Here we assume that column customer_id is the primary key column.
| order_number | customer_id | customer_name | amount | status |
|---|---|---|---|---|
| #SO_100001 | #CU_200001 | Jackie Harmon | $1000.00 | Open |
| #SO_100002 | #CU_200002 | Iris Wilkerson | $1200.80 | Confirmed |
| #SO_100003 | #CU_200003 | Josh Warner | $1350.50 | Closed |
| #SO_100004 | #CU_200001 | Jackie Harmon | $980.00 | Open |
| #SO_100005 | #CU_200004 | Jessica Day | $870.40 | Open |
| #SO_100006 | #CU_200002 | Iris Wilkerson | $450.60 | Confirmed |
| #SO_100007 | #CU_200003 | Josh Warner | $770.30 | Closed |
| #SO_100008 | #CU_200004 | Jessica Day | $890.50 | Canceled |
| #SO_100009 | #CU_200005 | Iris Wilkerson | $950.60 | Canceled |
| #SO_100010 | #CU_200001 | Jackie Harmon | $1120.00 | Open |
SUM() without Conditions
The following MySQL query calculates total amounts in the orders table:
SELECT SUM(amount)
FROM orders;
Because no condition is specified, MySQL will sum all amounts in the amount column.
SUM() with Conditions
In this example, suppose you want to calculate the total amount ordered by customer Iris Wilkerson. The SELECT statement looks like this:
SELECT SUM(amount)
FROM orders
WHERE customer_name = "Iris Wilkerson";
As you can see in the above table, SUM() will sum the values of 1200.80, 450.60, and 950.60 because three records meet the specified condition.
Let's take a look at the table above. Two customers have Iris Wilkerson with the same name, but the customer Id is different. So, SUM() will return the total amounts of these two customers.
To sum all the amounts for a unique customer, you need to use the customer_id column instead because the customer_id column is the primary key column and can only store unique values.
Here is an example:
SELECT SUM(amount)
FROM orders
WHERE customer_id ="#CU_200002";
If you only want to get the total amount of the confirmed order, you can write a SQL statement like this:
SELECT SUM(amount)
FROM orders
WHERE status = "Confirmed";
MySQL retrieves the records whose status has been confirmed and sums all the values in the "amount" column.
Summary
In this tutorial, you have learned how to sum a set of values using the MySQL SUM() function. You can use SUM() to calculate the total of all rows or only those that meet the specified criteria.