Count Rows of Fields (Columns) in MySQL Using Count IF Functions

Continuing the previous article, this time, we’ll discuss how to count the number of rows on fields (columns) with certain conditions in MySQL.

In this tutorial, we’ll use built-in MySQL functions that are: COUNT and IF, the both functions are powerful in day to day use, so, if you work with MySQL a lot, you should master that functions.

The Preparations…

Before we going any further, let’s prepare some data. Same as the previous tutorial, we use three tables here: product, sales, and customer. Tables and relationships between tables are same too, that looks like the following figure:

Count Rows of Fields in MySQL Using Count If Functions

The above file contains all files used in this tutorial. You can simply load the .sql file using SQL manager application and all the necessary tables and data would be created.

In order to easy to understand the queries discussed in this tutorial, we need to know what the tables look like. Well, the main table is the sales table, it look likes the following:

+--------+---------+------------+--------------+--------+
| 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 |
+--------+---------+------------+--------------+--------+

The next table is customer table:

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

The last is product table:

+------------+-----------------+
| product_id | name            |
+------------+-----------------+
| 111        | Refrigerator    |
| 112        | Television      |
| 113        | Air Cooler      |
| 114        | Air Conditioner |
| 115        | Laptop          |
+------------+-----------------+

Using The COUNT and IF Functions

As we already know, to calculate rows in the table, we use the COUNT function that will automatically count the amount of the existing rows.

We can specify columns we want to calculate in two ways:

  • COUNT(*) will count all rows in all the columns. The result will be taken from the column that has the highest result.
  • COUNT(FIELD_NAME) that will calculate the row for a particular field (column).

Furthermore, we can use the COUNT function with or without the DISTINCT clause:

  • COUNT to count all the rows even if there are duplications of data.
  • COUNT( DISTINCT ...) to calculate unique rows (rows with same values will not be counted).
Important to note that COUNT function will count all rows with any value including (0) value and empty string (”), but will NOT count rows that have NULL value. So be careful while using this function especially in complex query as it can give unexpected result

Continuing the previous article, now, we’ll count the number of items sold based on the sales year, we use the query:

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

The Result is:

+-----------+----------+-----------+----------+
| item_2016 | sum_2016 | item_2015 | sum_2015 |
+-----------+----------+-----------+----------+
|         8 |     1613 |         5 |      996 |
+-----------+----------+-----------+----------+

In the above query, MySQL will:

  • Execute the YEAR function to get the year of the date of amount column. If it results 2016, then include the value of the amount field to the COUNT function, otherwise, include the NULL value. Then, the IF function will use this value as the return value. This process also happened if the YEAR function results 2015.
  • Next, MySQL will perform the COUNT function. The counting process based on the return value of the IF function (will count any values except NULL), therefore, we use NULL instead of 0 as we use in SUM function.

Note that there is no space between COUNT and the open parenthesis, for example COUNT ( .Otherwise, the query will be stopped and triggered an error message: SQL Error (1630): SQL Error (1630): FUNCTION tutorial_sum_if.COUNT 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 to test ONE condition, for example, we can’t perform this test: IF(YEAR(payment_date) = 2016 AND product_id = '111')). If you want to test more than one condition, use the CASE clause instead.

So, be careful when using the IF function.

Improve The Query…

Furthermore, let’s group the results by a particular field (column) in this case the cust_id column, use the GROUP BY clause to accomplish the task, the query is:

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

The result is:

+---------+-----------+----------+-----------+----------+
| cust_id | item_2016 | sum_2016 | item_2015 | sum_2015 |
+---------+-----------+----------+-----------+----------+
|       1 |         4 |      523 |         1 |      245 |
|       2 |         1 |      150 |         2 |      400 |
|       3 |         2 |      650 |         1 |      273 |
|       4 |         1 |      290 |         1 |       78 |
+---------+-----------+----------+-----------+----------+

In the above query, we put the cust_id column into the GROUP BY clause, therefore, MySQL counts the rows based on that column. Lastly, let us show the customer name by combining the sales table and the customer table. The query is:

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

The result is:

+---------+---------+-----------+----------+-----------+----------+
| cust_id | name    | item_2016 | sum_2016 | item_2015 | sum_2015 |
+---------+---------+-----------+----------+-----------+----------+
|       1 | Alfa    |         4 |      523 |         1 |      245 |
|       2 | Beta    |         1 |      150 |         2 |      400 |
|       3 | Charlie |         2 |      650 |         1 |      273 |
|       4 | Delta   |         1 |      290 |         1 |       78 |
+---------+---------+-----------+----------+-----------+----------+

In the above query, we use the JOIN clause to combine the sales and customer table. The USING clause tells MySQL that we match the both tables using the cust_id column that exists in both tables.

Furthermore…

COUNT and IF function is often used to create a pivot table / crosstab. The following tutorials discuss in depth on how to create a pivot table using MySQL:

We have learned how to use COUNT and IF functions to count rows of columns in MySQL. In the real world, the situation may vary, so, if your case differs from what we have discussed here, feel free to ask a question using the comment form bellow this article.

Subscibe Now

Loves articles on webdevzoom.com? join our newsletter to get quality article right to your inbox. Nothing else, just quality stuff!!!

Related Post

Leave a comment

Like Us

Newsletter

Great information from webdevzoom.com right to your inbox

We value your privacy

Social