Tuesday 17 July 2018

Pivot Table Using MySQL – A Complete Guide

Pivot Table Using MySQL – A Complete Guide

Today, we will discuss how to create a pivot table using MySQL.
The term pivot tables usually used in Microsoft Excel. This technique typically used to make a summary of the data by converting rows into columns, this is what we will discuss here using MySQL.
In MySQL, we can not directly create a pivot table like in any other database that has a function named PIVOT, instead, we have to define the columns one by one.
This is in line with the concept that SQL is not a dynamic language that can perform loop such as for, while, foreach statement that exists in other programming languages such as PHP. As a static language, we have to define the query manually.
However, for dynamic data, we can still build a query dynamically. But it still not possible to write directly, we need to do some workaround, the discussion of this topic is quite long, so we do not discuss here.

I. The Preparation

To be able to create a pivot table using MySQL, of course, we need data, to make it easier, we will use just one table instead of multiple tables, named sales_table
The table consists of five fields: trx_id, name, trx_date, team, and amount. The contents of the table looks like the following:
trx_idnamatrx_dateteamamout
1Alfa2016-01-101250
2Charlie2016-01-022175
3Bravo2016-01-011310
4Bravo2016-02-041250
5Alfa2016-01-151300
6Charlie2016-01-132325
7Bravo2016-02-071275
8Bravo2016-03-061150
9Alfa2016-02-051215
10Alfa2016-02-221350
11Alfa2016-02-021450
12Alfa2016-03-121150
13Alfa2016-03-171225
14Bravo2016-03-111150
15Bravo2016-03-181150
16Charlie2016-01-232350
17Charlie2016-02-222300
18Charlie2016-03-212275
19Charlie2016-03-122450
20Delta2016-02-112450
21Delta2016-02-172550
22Delta2016-03-112370
The above table quite informative, but we can make it more informative by converting rows into columns, which very useful when we need comparing the data horizontally.

II. Pivot Table Using MySQL

This time, we’ll create a pivot table by converting date transaction into columns, so the resulted table will provide information about the sales performance of each officer, each month. The SQL query looks like follows:
SELECT  name,
 SUM( IF( MONTH(trx_date) = 1, amount, 0) ) AS january,
 SUM( IF( MONTH(trx_date) = 2, amount, 0) ) AS february,
 SUM( IF( MONTH(trx_date) = 3, amount, 0) ) AS march,
 SUM( amount ) AS total
FROM sales_table
GROUP BY name
The result:
+---------+---------+----------+-------+-------+
| name    | january | february | march | total |
+---------+---------+----------+-------+-------+
| Alfa    |     375 |      815 |   375 |  1565 |
| Bravo   |     310 |      525 |   825 |  1660 |
| Charlie |     850 |      300 |   725 |  1875 |
| Delta   |       0 |     1000 |   370 |  1370 |
+---------+---------+----------+-------+-------+
The resulted table is much more informative than the original table, we can easily compare sales data per month.
In the above query, we use the SUM function to calculate the data for each column, more about this topic can be read in the article: Using IF SUM to add up the value of the field / column of a table in MySQL
In addition, we also use the MONTH function to get the month of the date in the trx_date column, if the result is 1, then we placed it into the January column, if 2 then to February column, and if 3 then to March column, last, we use SUM(nilai_trx) to create the total column.

Completing The Displayed Data

To be more informative, let’s add columns that contain a number of transactions. We change the query to be something like this:
SELECT  
 name,
 COUNT( IF( MONTH(trx_date) = 1, amount, NULL) ) AS trx_1,
 SUM( IF( MONTH(trx_date) = 1, amount, 0) ) AS january,
 COUNT( IF( MONTH(trx_date) = 2, amount, NULL) ) AS trx_2,
 SUM( IF( MONTH(trx_date) = 2, amount, 0) ) AS februay,
 COUNT( IF( MONTH(trx_date) = 3, amount, NULL) ) AS trx_3,
 SUM( IF( MONTH(trx_date) = 3, amount, 0) ) AS march,
 
 COUNT(trx_date) AS trx_num,
 SUM( amount ) AS total_trx
FROM sales_table
GROUP BY name
The result:
+---------+-------+---------+-------+---------+-------+-------+---------+-----------+
| name    | trx_1 | january | trx_2 | februay | trx_3 | march | trx_num | total_trx |
+---------+-------+---------+-------+---------+-------+-------+---------+-----------+
| Alfa    |     2 |     375 |     3 |     815 |     2 |   375 |       7 |      1565 |
| Bravo   |     1 |     310 |     2 |     525 |     3 |   825 |       6 |      1660 |
| Charlie |     3 |     850 |     1 |     300 |     2 |   725 |       6 |      1875 |
| Delta   |     0 |       0 |     2 |    1000 |     1 |   370 |       3 |      1370 |
+---------+-------+---------+-------+---------+-------+-------+---------+-----------+
In the query above, I add a blank line for readability purpose.
In the query above, we use the COUNT function to count the number of rows in the amount column, more about this function can be read in the article: Calculate Column on MySQL Table With Specific Conditions Using COUNT IF
Note that in the COUNT function, COUNT(IF(MONTH(tgl_trx) = 1, id_trx, NULL)) AS trx_1, we use NULL not 0 as we use on the SUM function.
This is because the COUNT function will still count the rows with a value of 0 which in some condition will make inaccurate results, so we use NULL instead of 0

III. Pivot Tables Using MySQL – Adding Total Row Using WITH ROLLUP Clause

We can still improve the above table by adding rows that contain total value of transactions per month, so let’s do it by running the following query:
SELECT  
 IFNULL(name, 'TOTAL') AS officer_name,
 COUNT( IF( MONTH(trx_date) = 1, amount, NULL) ) AS trx_1,
 SUM( IF( MONTH(trx_date) = 1, amount, 0) ) AS january,
 COUNT( IF( MONTH(trx_date) = 2, amount, NULL) ) AS trx_2,
 SUM( IF( MONTH(trx_date) = 2, amount, 0) ) AS februay,
 COUNT( IF( MONTH(trx_date) = 3, amount, NULL) ) AS trx_3,
 SUM( IF( MONTH(trx_date) = 3, amount, 0) ) AS march,
 
 COUNT(trx_date) AS trx_num,
 SUM( amount ) AS total
FROM sales_table
GROUP BY name
WITH ROLLUP
The result is:
+--------------+-------+---------+-------+---------+-------+-------+---------+-------+
| officer_name | trx_1 | january | trx_2 | februay | trx_3 | march | trx_num | total |
+--------------+-------+---------+-------+---------+-------+-------+---------+-------+
| Alfa         |     2 |     375 |     3 |     815 |     2 |   375 |       7 |  1565 |
| Bravo        |     1 |     310 |     2 |     525 |     3 |   825 |       6 |  1660 |
| Charlie      |     3 |     850 |     1 |     300 |     2 |   725 |       6 |  1875 |
| Delta        |     0 |       0 |     2 |    1000 |     1 |   370 |       3 |  1370 |
| TOTAL        |     6 |    1535 |     8 |    2640 |     8 |  2295 |      22 |  6470 |
+--------------+-------+---------+-------+---------+-------+-------+---------+-------+
In the example above, there is a new row named TOTAL, so now we’ve been able to compare the number of sales and total of sales per month.
In the above query, WITH ROLLUP clause will add the total row, that clause will calculate any rows resulted by aggregate function. All of the columns of the resulted table are aggregated except officer_name column.
Furthermore, in the total row, MySQL will provide a NULL value in the column used in the GROUP BY clause, which in the example above, the name column.
To be meaningful, we replace the NULL value to a word of TOTAL, to do so, we use the IFNULL function – IFNULL(name, 'TOTAL') AS officer_name, which means if the value of the column is NULL, then replace it with the word of TOTAL.

Adding Subtotal Row at Pivot Table

Furthermore, in certain circumstances, we need to display a subtotal row. Related to the above example, in order to know the performance of each team, now, we will create subtotals based on the team value.
Query that we used:
SELECT  
 IFNULL( name, 'SUB TOTAL' ) AS officer_name,
 IFNULL( team, 'TOTAL' ) AS officer_team,
 
 COUNT( IF( MONTH(trx_date) = 1, amount, NULL) ) AS trx_1,
 SUM( IF( MONTH(trx_date) = 1, amount, 0) ) AS january,
 COUNT( IF( MONTH(trx_date) = 2, amount, NULL) ) AS trx_2,
 SUM( IF( MONTH(trx_date) = 2, amount, 0) ) AS februay,
 COUNT( IF( MONTH(trx_date) = 3, amount, NULL) ) AS trx_3,
 SUM( IF( MONTH(trx_date) = 3, amount, 0) ) AS march,
 
 COUNT(trx_date) AS trx_num,
 SUM( amount ) AS total_trx
FROM sales_table
GROUP BY team, name
WITH ROLLUP
The result is:
+--------------+--------------+-------+---------+-------+---------+-------+-------+---------+-----------+
| officer_name | officer_team | trx_1 | january | trx_2 | februay | trx_3 | march | trx_num | total_trx |
+--------------+--------------+-------+---------+-------+---------+-------+-------+---------+-----------+
| Alfa         | 1            |     2 |     375 |     3 |     815 |     2 |   375 |       7 |      1565 |
| Bravo        | 1            |     1 |     310 |     2 |     525 |     3 |   825 |       6 |      1660 |
| SUB TOTAL    | 1            |     3 |     685 |     5 |    1340 |     5 |  1200 |      13 |      3225 |
| Charlie      | 2            |     3 |     850 |     1 |     300 |     2 |   725 |       6 |      1875 |
| Delta        | 2            |     0 |       0 |     2 |    1000 |     1 |   370 |       3 |      1370 |
| SUB TOTAL    | 2            |     3 |     850 |     3 |    1300 |     3 |  1095 |       9 |      3245 |
| SUB TOTAL    | TOTAL        |     6 |    1535 |     8 |    2640 |     8 |  2295 |      22 |      6470 |
+--------------+--------------+-------+---------+-------+---------+-------+-------+---------+-----------+
Note that there are new rows that represent SUB TOTAL, that rows added because we are adding a new column to the GROUP BY clause that is team, so that when the WITH ROLLUP clause is executed, MySQL will calculate subtotals according to team + name columns.
Notice that in the GROUP BY clause, the  different order of the column will give a different result, you can try it by changing the order of the column from team, name into name, team and then see the result.

Changing The Order of The Pivot Table Data

Notice that any data generated by the GROUP BY clause will be displayed sequentially, the data will be sorted ascending, started from the smallest to the largest value.
For example, in the previous query, the data in the officer_name column sorted alphabetically from smallest to the largest, as well as the team column.
Why be like that? this is because when running the GROUP BY clause, MySQL will also sort the data in ascending order (from smallest to largest) which is often called the implicit order.
We can change this behavior by adding ASC or DESC option to the GROUP BY clause. Suppose we want to change the order of the names and teams column into descending order.
Run the following query:
SELECT  
 IFNULL( name, 'SUB TOTAL' ) AS officer_name,
 IFNULL( team, 'TOTAL' ) AS officer_team,
 
 COUNT( IF( MONTH(trx_date) = 1, amount, NULL) ) AS trx_1,
 SUM( IF( MONTH(trx_date) = 1, amount, 0) ) AS january,
 COUNT( IF( MONTH(trx_date) = 2, amount, NULL) ) AS trx_2,
 SUM( IF( MONTH(trx_date) = 2, amount, 0) ) AS februay,
 COUNT( IF( MONTH(trx_date) = 3, amount, NULL) ) AS trx_3,
 SUM( IF( MONTH(trx_date) = 3, amount, 0) ) AS march,
 
 COUNT(trx_date) AS trx_num,
 SUM( amount ) AS total_trx
FROM sales_table
GROUP BY team DESC, name DESC
WITH ROLLUP
The result:
+--------------+--------------+-------+---------+-------+---------+-------+-------+---------+-----------+
| officer_name | officer_team | trx_1 | january | trx_2 | februay | trx_3 | march | trx_num | total_trx |
+--------------+--------------+-------+---------+-------+---------+-------+-------+---------+-----------+
| Delta        | 2            |     0 |       0 |     2 |    1000 |     1 |   370 |       3 |      1370 |
| Charlie      | 2            |     3 |     850 |     1 |     300 |     2 |   725 |       6 |      1875 |
| SUB TOTAL    | 2            |     3 |     850 |     3 |    1300 |     3 |  1095 |       9 |      3245 |
| Bravo        | 1            |     1 |     310 |     2 |     525 |     3 |   825 |       6 |      1660 |
| Alfa         | 1            |     2 |     375 |     3 |     815 |     2 |   375 |       7 |      1565 |
| SUB TOTAL    | 1            |     3 |     685 |     5 |    1340 |     5 |  1200 |      13 |      3225 |
| SUB TOTAL    | TOTAL        |     6 |    1535 |     8 |    2640 |     8 |  2295 |      22 |      6470 |
+--------------+--------------+-------+---------+-------+---------+-------+-------+---------+-----------+
In the table above, the officer name and officer_team are sorted descendingly.
This method is the easiest way to change the order of the data, however, on MySQL version 5.7, this feature has been deprecated, meaning that it not recommended for use, because, in the future version this feature will be removed.
Because of that reason, It is recommended to use an explicit order by using the ORDER BY clause. In a query that contains the WITH ROLLUP clause, using the ORDER BY clause is not an easy task.
This is because we must define the value of the column resulted by WITH ROLLUP clause such a way so that it will be placed at the bottom of the row. Consider the following example:
SELECT * FROM
(
 SELECT  
  IFNULL( name, '0-SUB TOTAL' ) AS officer_name,
  IFNULL( team, '0-TOTAL' ) AS officer_team,
  
  COUNT( IF( MONTH(trx_date) = 1, amount, NULL) ) AS trx_1,
  SUM( IF( MONTH(trx_date) = 1, amount, 0) ) AS january,
  COUNT( IF( MONTH(trx_date) = 2, amount, NULL) ) AS trx_2,
  SUM( IF( MONTH(trx_date) = 2, amount, 0) ) AS februay,
  COUNT( IF( MONTH(trx_date) = 3, amount, NULL) ) AS trx_3,
  SUM( IF( MONTH(trx_date) = 3, amount, 0) ) AS march,
  
  COUNT(trx_date) AS trx_num,
  SUM( amount ) AS total_trx
 FROM sales_table
 GROUP BY team, name
 WITH ROLLUP
) AS sales_table
ORDER BY officer_team DESC, officer_name DESC
The result is:
+--------------+--------------+-------+---------+-------+---------+-------+-------+---------+-----------+
| officer_name | officer_team | trx_1 | january | trx_2 | februay | trx_3 | march | trx_num | total_trx |
+--------------+--------------+-------+---------+-------+---------+-------+-------+---------+-----------+
| Delta        | 2            |     0 |       0 |     2 |    1000 |     1 |   370 |       3 |      1370 |
| Charlie      | 2            |     3 |     850 |     1 |     300 |     2 |   725 |       6 |      1875 |
| 0-SUB TOTAL  | 2            |     3 |     850 |     3 |    1300 |     3 |  1095 |       9 |      3245 |
| Bravo        | 1            |     1 |     310 |     2 |     525 |     3 |   825 |       6 |      1660 |
| Alfa         | 1            |     2 |     375 |     3 |     815 |     2 |   375 |       7 |      1565 |
| 0-SUB TOTAL  | 1            |     3 |     685 |     5 |    1340 |     5 |  1200 |      13 |      3225 |
| 0-SUB TOTAL  | 0-TOTAL      |     6 |    1535 |     8 |    2640 |     8 |  2295 |      22 |      6470 |
+--------------+--------------+-------+---------+-------+---------+-------+-------+---------+-----------+
In the above query, the subquery will create a temporary table in exact form with the previous example, then we sort that table in a descending order based on officer_team and officer_name column .
Note that in the above query, we give a prefix 0- to the SUB TOTAL value, so that when the column sorted descendingly, that row will be placed at the bottom, this is same as the TOTAL value on the officer_team column.
You can learn more about calculating total and grand total in MySQL in the article: Calculating Total and Subtotal Using MySQL

IV. Pivot Table Using MySQL – Adding Total Row Using Independent Query

In the previous example, we can easily add total and subtotal row using the WITH ROLLUP clause, in certain circumstances, we can not rely on that clause, for example, when we want to display data based on the amount column in a descending order.
If we use the WITH ROLLUP clause, then the TOTAL row will always be placed on the top, this is because that row will always have the biggest value. To solve this, we can add a TOTAL row using an additional query with UNION ALL clause like the following:
SELECT * FROM (
 SELECT  
  name,
  team,
   
  SUM( IF( MONTH(trx_date) = 1, amount, 0) ) AS january,
  COUNT( IF( MONTH(trx_date) = 1, trx_date, NULL) ) AS trx_1,
  SUM( IF( MONTH(trx_date) = 2, amount, 0) ) AS february,
  COUNT( IF( MONTH(trx_date) = 2, amount, NULL) ) AS trx_2,
  SUM( IF( MONTH(trx_date) = 3, amount, 0) ) AS march,
  COUNT( IF( MONTH(trx_date) = 3, amount, NULL) ) AS trx_3,
  
  COUNT(trx_date) AS jml_trx,
  SUM( amount ) AS total
 FROM sales_table
 GROUP BY team,  name
 ORDER BY total DESC
) AS sales_table
UNION ALL
SELECT 'TOTAL' AS name, '' AS team, 
 SUM( IF( MONTH(trx_date) = 1, amount, 0) ) AS january,
 COUNT( IF( MONTH(trx_date) = 1, trx_date, NULL) ) AS trx_1,
 SUM( IF( MONTH(trx_date) = 2, amount, 0) ) AS february,
 COUNT( IF( MONTH(trx_date) = 2, amount, NULL) ) AS trx_2,
 SUM( IF( MONTH(trx_date) = 3, amount, 0) ) AS march,
 COUNT( IF( MONTH(trx_date) = 3, amount, NULL) ) AS trx_3,
 COUNT(trx_date) AS trx_num,
 SUM( amount ) AS total
FROM sales_table
The result:
+---------+------+---------+-------+----------+-------+-------+-------+---------+-------+
| name    | team | january | trx_1 | february | trx_2 | march | trx_3 | jml_trx | total |
+---------+------+---------+-------+----------+-------+-------+-------+---------+-------+
| Charlie | 2    |     850 |     3 |      300 |     1 |   725 |     2 |       6 |  1875 |
| Bravo   | 1    |     310 |     1 |      525 |     2 |   825 |     3 |       6 |  1660 |
| Alfa    | 1    |     375 |     2 |      815 |     3 |   375 |     2 |       7 |  1565 |
| Delta   | 2    |       0 |     0 |     1000 |     2 |   370 |     1 |       3 |  1370 |
| TOTAL   |      |    1535 |     6 |     2640 |     8 |  2295 |     8 |      22 |  6470 |
+---------+------+---------+-------+----------+-------+-------+-------+---------+-------+
Note that in the above query, we put the first query in a subquery, this is because when using UNION, the ORDER BY clause must be placed on the bottom, so, in order to sort the first data, we use subquery.

V. Dynamic Pivot Table Using MySQL

In the above discussion, we define the columns of pivot table manually. In many circumstances, it is sufficient, however, on certain conditions, the number of columns are dynamics according to the number of rows.
In such conditions, the SQL syntax always changes, so the static method can not be used anymore, to solve this, we can use a technique known as dynamic pivot table. A discussion of this topic can be followed on Dynamic Pivot Table Using MySQL

VI. Wrap Up

Creating a pivot table using MySQL is a quite easy task, the main obstacle to this process is when we want to sort the data that contain subtotal and total rows generated by WITH ROLLUP clause.
This is because the total and subtotal row also sorted, so that rows has a lot of possibilities to be placed on the top, as an alternative, we can add a total row using independent query.
If you want to create a pivot table using PHP, you can follow the tutorial: Pivot Table Using PHP and MySQL

0 comments:

Post a Comment

Subscribe to: Post Comments (Atom)