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

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.

`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:

`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 insteadSo 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.