Using SUM and IF Functions to Sum Values of Fields (Columns) in MySQL

Today we’ll discuss how to use SUM and IF function to sum values of rows in a particular column (field) of a table in MySQL. Hopefully in some examples in the next article, we can discuss other various functions provided by MySQL.

The Preparations…

Before going any further, we need to prepare some data. Here, we’ll use three tables named: sales, customer, and product. The table’s schema and the relationship between tables look like the following figure:

Using Sum If Function to Sum Values of Fileds in MySQL - ERD Diagram

You can download files used in this tutorial by clicking the button above. In that file, there is a SQL file that can be used to create all the necessary tables and all necessary data.

In this tutorial, we’ll work a lot with the sales table data, to be more easily understood, here is some explanation of the fields of the sales table:

  • trx_id contains a unique value. This field is the primary key of the sales table.
  • cust_id contains an id number of the customer. This field is a foreign key of cust_id field of the customer table.
  • product_id contains an id number of the product. This field is a foreign key of the product_id field of product table.
  • payment_date contains date of payment in format yyyy-mm-dd format.
  • amount contains the amount of transaction.

The content of the sales table as shown in the following table:

+--------+---------+------------+--------------+--------+
| trx_id | cust_id | product_id | payment_date | amount |
+--------+---------+------------+--------------+--------+
|      1 |       1 | 111        | 2016-02-02   |    150 |
|      2 |       1 | 112        | 2016-03-10   |    125 |
|      3 |       1 | 113        | 2016-04-10   |     75 |
|      4 |       2 | 112        | 2015-02-02   |     50 |
|      5 |       2 | 112        | 2015-03-10   |    350 |
|      6 |       2 | 113        | 2016-04-10   |    150 |
|      7 |       3 | 115        | 2016-02-02   |    155 |
|      8 |       3 | 115        | 2015-03-10   |    273 |
|      9 |       3 | 111        | 2016-04-10   |    495 |
|     10 |       1 | 115        | 2015-02-02   |    245 |
|     11 |       1 | 111        | 2016-03-10   |    173 |
|     12 |       4 | 111        | 2015-04-10   |     78 |
|     13 |       4 | 115        | 2016-04-10   |    290 |
+--------+---------+------------+--------------+--------+

While for the customer table, the data look like the following:

+-------------+---------+
| customer_id | name    |
+-------------+---------+
|           1 | Alfa    |
|           2 | Beta    |
|           3 | Charlie |
|           4 | Delta   |
+-------------+---------+

Using The SUM – IF Functions

Next, let’s use the SUM and IF function to perform a summation of all sales grouped by year. The query that we use is:

SELECT  SUM(IF( YEAR(payment_date) = 2016, amount, 0)) AS sum_2016,
        SUM(IF( YEAR(payment_date) = 2015, amount, 0)) AS sum_2015
FROM sales

The result is:

+----------+----------+
| sum_2016 | sum_2015 |
+----------+----------+
|     1613 |      996 |
+----------+----------+

In the above query, MySQL will examine all of the rows one by one. First, MySQL will execute the YEAR function to obtain the year of the date on payment_date column, then:

  • If the YEAR function result 2016, then retrieve the value of the payment_date column to be included in the summation process, otherwise, use a zero (0) value. The same process also occurs if the YEAR function results 2015.
  • Next, MySQL will perform the SUM function. The summing process based on the value returned by the IF function.

NOTE that there is no space exists between SUM and the open parenthesis, such as: SUM ( .Otherwise, it will display an error message: SQL Error (1630): FUNCTION tutorial_sum_if.SUM does not exist. Check the 'Function Name Parsing and Resolution' section in the Reference Manual

The use of IF function:

The IF function can only be used for testing one condition, for example, we can’t perform this: SUM(IF(YEAR(payment_date) = 2016 AND product_id = '111')). So, if we want to add a column with values under multiple conditions, then, use the CASE clause instead

So be careful when testing condition in MySQL.

Improve The Query…

Furthermore, if we want to know the amount of purchases per customer per year, then we can group the data by cust_id column, the query will look like this:

SELECT  cust_id,
        SUM(IF( YEAR(payment_date) = 2016, amount, 0)) AS sum_2016,
        SUM(IF( YEAR(payment_date) = 2015, amount, 0)) AS sum_2015,
        SUM(amount) AS TOTAL
FROM sales
GROUP BY cust_id

The Result:

+---------+----------+----------+-------+
| cust_id | sum_2016 | sum_2015 | TOTAL |
+---------+----------+----------+-------+
|       1 |      523 |      245 |   768 |
|       2 |      150 |      400 |   550 |
|       3 |      650 |      273 |   923 |
|       4 |      290 |       78 |   368 |
+---------+----------+----------+-------+

Finally, let us show the customer name information by combining the sales table and the customer table. The query that we use:

SELECT  cust_id,
	name,
 	SUM(IF( YEAR(payment_date) = 2016, amount, 0)) AS sum_2016,
        SUM(IF( YEAR(payment_date) = 2015, amount, 0)) AS sum_2015,
        SUM(amount) AS TOTAL
FROM sales
LEFT JOIN customer USING(cust_id)
GROUP BY cust_id

The result:

+---------+---------+----------+----------+-------+
| cust_id | name    | sum_2016 | sum_2015 | TOTAL |
+---------+---------+----------+----------+-------+
|       1 | Alfa    |      523 |      245 |   768 |
|       2 | Beta    |      150 |      400 |   550 |
|       3 | Charlie |      650 |      273 |   923 |
|       4 | Delta   |      290 |       78 |   368 |
+---------+---------+----------+----------+-------+

If we want to count the number of rows, for example, to calculate the number of transactions per customer, we can use the COUNT function. The Discussion of this function can be followed here: Count Rows of Fields (Columns) in MySQL Using Count IF Functions

Adding a Grand Total Row

There are several ways to make a grand total row, some of which are discussed in the article: Calculating Total and Subtotal in MySQL.

In this example, we will calculate the grand total in a separate query then combining it with the main query using UNION ALL clause, the query looks like the following:

SELECT  cust_id,
	name,
        SUM(IF(YEAR(payment_date) = 2016, amount, 0)) AS sum_2016,
        SUM(IF(YEAR(payment_date) = 2015, amount, 0)) AS sum_2015,
        SUM(amount) AS TOTAL
FROM sales
LEFT JOIN customer USING(cust_id)
GROUP BY cust_id
UNION ALL
SELECT  "" AS cust_id,
	"Grand Total" as name,
        SUM(IF(YEAR(payment_date) = 2016, amount, 0)) AS sum_2016,
        SUM(IF(YEAR(payment_date) = 2015, amount, 0)) AS sum_2015,
        SUM(amount) AS TOTAL
FROM sales

The Result is:

+---------+-------------+----------+----------+-------+
| cust_id | name        | sum_2016 | sum_2015 | TOTAL |
+---------+-------------+----------+----------+-------+
| 1       | Alfa        |      523 |      245 |   768 |
| 2       | Beta        |      150 |      400 |   550 |
| 3       | Charlie     |      650 |      273 |   923 |
| 4       | Delta       |      290 |       78 |   368 |
|         | Grand Total |     1613 |      996 |  2609 |
+---------+-------------+----------+----------+-------+

In the above query, the first and second query looks similar, the difference is: in the first query, the data is grouped (using GROUP BY clause) based on the cust_id column.

While for the second query, we did not perform any group, as we employ the aggregate function (the SUM function) MySQL will automatically combine all the data on each column into a one row.

Wrap Up

In this tutorial, we have learned how to use SUM and IF function to perform a calculation of particular fields in MySQL. In the real world, the situation may vary, so if you have some cases that differ from what we have discussed here, you can leave a comment bellow.

Related Post

Leave a comment

Like Us

Social