MySQL SUM() Function – Get the Total Value with Examples

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:


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.

#SO_100001#CU_200001Jackie Harmon$1000.00Open
#SO_100002#CU_200002Iris Wilkerson$1200.80Confirmed
#SO_100003#CU_200003Josh Warner$1350.50Closed
#SO_100004#CU_200001Jackie Harmon$980.00Open
#SO_100005#CU_200004Jessica Day$870.40Open
#SO_100006#CU_200002Iris Wilkerson$450.60Confirmed
#SO_100007#CU_200003Josh Warner$770.30Closed
#SO_100008#CU_200004Jessica Day$890.50Canceled
#SO_100009#CU_200005Iris Wilkerson$950.60Canceled
#SO_100010#CU_200001Jackie Harmon$1120.00Open

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.


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.

See also:
MySQL LIKE Operator Pattern Matching and Examples
MySQL SUBSTRING_INDEX Function with Examples
MySQL EXISTS Operator with Examples
MySQL ROW_NUMBER Function with Examples
MySQL CONCAT() Function | Concatenate Strings in MySQL

Leave a Comment