MySQL SUM function – Calculate the sum of values

The MySQL SUM() function calculates the total of all values in a group. These values can come from a column or an expression. This article provides examples of different scenarios for calculating the sum of values in MySQL.

The following is the basic syntax for a query with the SUM() function:

1. SELECT SUM([DISTINCT] expression)
3. FROM table
5. WHERE condition(s);

Explanation

The expression can be a column or a formula. The DISTINCT keyword is optional, and we can specify it to calculate values for unique values of expression.

In MySQL, If there are no records returned, the function SUM() returns NULL.

If we are going to calculate the sum of values for grouped data, the syntax will be as follows:

1. SELECT column(s), SUM([DISTINCT] expression)
3. FROM table
5. WHERE condition(s)
7. GROUP BY column(s)
9. HAVING condition(s);

In the second query, the sum of the values will be calculated for each group as specified in the GROUP BY clause.

MySQL Examples to Calculate Sum of Values Using the SUM Function

Below are two sample tables, Order and OrderDetail. Table OrderData contains general information on the orders, such as Order ID, CustomerID, Order Status, and Order Date. Table OrderDetail contains detailed information on each order item, such as SKU, Unit Cost, and Quantity.

We will use these tables to illustrate ways on how we can calculate the sum of values using the SUM() function.

Table: OrderData

Table: OrderDetail

Example #1: Calculate Total Quantity Ordered

If we want to get the total quantity ordered (regardless of the order status), we can use the SUM() function on the column Quantity from table OrderDetail. Our query is as follows:

1. SELECT SUM(Quantity) AS TotalQuantity
3. FROM OrderDetail;

Result:

Example #2: Calculate Total Unit Price of all Unique Products

Let’s say we want to get the total unit price for all of the unique products ordered. This means that multiple orders for the same SKU should only be considered once. In our sample table OrderDetail, we have two products PT-BLK-0001 and PT-RED-0001, priced at 120 and 135 units, respectively.

To get the desired result, we add the DISTINCT keyword within the SUM() function, and our query is as follows:

1. SELECT SUM(DISTINCT UnitPrice) AS TotalUniqueProductPrice
3. FROM OrderDetail;

Result:

Example #3: Calculate Total Sales for Confirmed Orders

In this example, we begin by identifying all the information needed to create the query.

For us to identify confirmed orders, we need to perform a JOIN on the Order Data table, and filter the Order Status using a WHERE clause.

Furthermore, note that Total Sales is not an existing column. We can calculate this value by multiplying the Unit Price with the Quantity Sold, resulting in the expression UnitPrice * Quantity.

The steps are outlined as follows:

Step #1: Perform a JOIN on the tables, and filter Confirmed Orders using WHERE

Our query is as follows:

1. SELECT D.*
3. FROM OrderDetail D
5. INNER JOIN OrderData O
7. ON D.OrderID = O.OrderID
9. WHERE O.OrderStatus = ‘Confirmed’;

Step #2: Calculate the Total Sales for each Product

Our query is as follows:

1. SELECT D.UnitPrice * D.Quantity
3. FROM OrderDetail D
5. INNER JOIN OrderData O
7. ON D.OrderID = O.OrderID
9. WHERE O.OrderStatus = ‘Confirmed’;

Step #3: Calculate the Total Sales using SUM

Our query is as follows:

1. SELECT SUM(D.UnitPrice * D.Quantity) AS TotalSales
3. FROM OrderDetail D
5. INNER JOIN OrderData O
7. ON D.OrderID = O.OrderID
9. WHERE O.OrderStatus = ‘Confirmed’;

This example shows us how we can calculate totals under specific conditions. The query also showed us how to use the SUM() function on expressions.

Result:

Example #4: Filtering by SUM Values

We can also filter our results based on total values. Since SUM is an aggregate function, we cannot add in the WHERE clause. Aggregate functions like SUM() can be used as a filter in the HAVING clause.

As an illustration, if we want to filter our records to include SKUs whose total quantity ordered is at least 200 units, then we formulate our query as follows:

1. SELECT SKU, SUM(Quantity) AS TotalQuantity
3. FROM OrderDetail
5. GROUP BY SKU
7. HAVING SUM(Quantity) >= 200;

Result:

In this example, we used the SUM() function as a filtering condition on groups.

How to Handle Totals in MySQL When There is No Record Retrieved

By default, if there are no records retrieved in SUM(), MySQL will output a NULL value. In some cases, however, we may prefer to output 0 instead of NULL.

We can use the COALESCE function to return a default value if there are no records retrieved from the query. Let’s say we want to get the total quantity ordered a product with SKU = ‘PT-WHT-0001’. We will have the original query as follows:

1. SELECT SUM(Quantity) AS TotalQuantity
3. FROM OrderDetail
5. WHERE SKU = ‘PT-WHT-0001’;

Result:

If we want to display a default value 0, we update the query as follows:

1. SELECT COALESCE(SUM(Quantity), 0) AS TotalQuantity
3. FROM OrderDetail
5. WHERE SKU = ‘PT-WHT-0001’;

Result:

The COALESCE() function checks if the value is NULL, and replaces it with the default value specified in the parameter. However, if there is a value for the expression, then the original value will be displayed.