MySQL SUM() Function with Examples


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:

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;

But, if you want to specify conditions, you can write like this:

SELECT SUM(expression)
FROM table
WHERE where_conditions;

SUM() Function Examples

Suppose you have the following orders table that consists of 5 columns, order_numbercustomer_idcustomer_nameamount, and status.

Here, we assume that column customer_id is a primary key column.

order_numbercustomer_idcustomer_nameamountstatus
#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

Let's say you want to calculate all the amount in the orders table, and then you can write the following MySQL query.

SELECT SUM(amount)
FROM orders;

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:


SELECT SUM(amount)
FROM orders
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:

SELECT SUM(amount)
FROM orders
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:

SELECT SUM(amount)
FROM orders
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.

Leave a Reply

Your email address will not be published. Required fields are marked *