Mysql – Action_id group by for last 3 days in 3 different columns

MySQL

My requirement is as below.

I have a table like this.

-----------------------------------
REQUEST  |  DATE        |  ACTION
-----------------------------------
1        |15-SEPT-2014  |  ACT1
2        |16-SEPT-2014  |  ACT1
3        |16-SEPT-2014  |  ACT2
4        |17-SEPT-2014  |  ACT1
5        |17-SEPT-2014  |  ACT2
6        |17-SEPT-2014  |  ACT3
-----------------------------------

Based on the above table I need the following format:

ACTION    | 15-SEPT-2014  | 16-SEPT-2014  | 17-SEPT-2014  |
-----------------------------------------------------------
ACT1      | COUNT (ACT1)  | COUNT (ACT1)  | COUNT (ACT1)  |
ACT2      | COUNT (ACT2)  | COUNT (ACT2)  | COUNT (ACT2)  |
ACT3      | COUNT (ACT3)  | COUNT (ACT3)  | COUNT (ACT3)  |

Best Answer

PROPOSED QUERY

SET @dt0 = '2014-09-17';
SET @dt1 = @dt0 - INTERVAL 1 DAY;
SET @dt2 = @dt0 - INTERVAL 2 DAY;
SET @sql = 'SELECT A.action ACTION,';
SET @sql = CONCAT(@sql,'SUM(IF(B.date=@dt2 AND B.action=A.action,1,0)) "',@dt2,'",');
SET @sql = CONCAT(@sql,'SUM(IF(B.date=@dt1 AND B.action=A.action,1,0)) "',@dt1,'",');
SET @sql = CONCAT(@sql,'SUM(IF(B.date=@dt0 AND B.action=A.action,1,0)) "',@dt0,'"');
SET @sql = CONCAT(@sql,'FROM (SELECT DISTINCT action FROM mytable ');
SET @sql = CONCAT(@sql,'WHERE date>=@dt2 AND date<=@dt0) A,mytable B GROUP BY A.action');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

SAMPLE DATA

mysql> drop database if exists alkb;
Query OK, 1 row affected (0.01 sec)

mysql> create database alkb;
Query OK, 1 row affected (0.00 sec)

mysql> use alkb
Database changed
mysql> create table mytable
    -> (
    ->     id int not null auto_increment primary key,
    ->     date date not null,
    ->     action varchar(10)
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql> insert into mytable (date,action) values
    -> ('2014-09-15','ACT1'),('2014-09-16','ACT1'),
    -> ('2014-09-16','ACT2'),('2014-09-17','ACT1'),
    -> ('2014-09-17','ACT2'),('2014-09-17','ACT3');
Query OK, 6 rows affected (0.01 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM mytable;
+----+------------+--------+
| id | date       | action |
+----+------------+--------+
|  1 | 2014-09-15 | ACT1   |
|  2 | 2014-09-16 | ACT1   |
|  3 | 2014-09-16 | ACT2   |
|  4 | 2014-09-17 | ACT1   |
|  5 | 2014-09-17 | ACT2   |
|  6 | 2014-09-17 | ACT3   |
+----+------------+--------+
6 rows in set (0.00 sec)

mysql>

PROPOSED QUERY EXECUTED

mysql> SET @dt0 = '2014-09-17';
Query OK, 0 rows affected (0.00 sec)

mysql> SET @dt1 = @dt0 - INTERVAL 1 DAY;
Query OK, 0 rows affected (0.00 sec)

mysql> SET @dt2 = @dt0 - INTERVAL 2 DAY;
Query OK, 0 rows affected (0.00 sec)

mysql> SET @sql = 'SELECT A.action ACTION,';
Query OK, 0 rows affected (0.00 sec)

mysql> SET @sql = CONCAT(@sql,'SUM(IF(B.date=@dt2 AND B.action=A.action,1,0)) "',@dt2,'",');
Query OK, 0 rows affected (0.00 sec)

mysql> SET @sql = CONCAT(@sql,'SUM(IF(B.date=@dt1 AND B.action=A.action,1,0)) "',@dt1,'",');
Query OK, 0 rows affected (0.00 sec)

mysql> SET @sql = CONCAT(@sql,'SUM(IF(B.date=@dt0 AND B.action=A.action,1,0)) "',@dt0,'"');
Query OK, 0 rows affected (0.00 sec)

mysql> SET @sql = CONCAT(@sql,'FROM (SELECT DISTINCT action FROM mytable ');
Query OK, 0 rows affected (0.00 sec)

mysql> SET @sql = CONCAT(@sql,'WHERE date>=@dt2 AND date<=@dt0) A,mytable B GROUP BY A.action');
Query OK, 0 rows affected (0.00 sec)

mysql> PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;
Query OK, 0 rows affected (0.00 sec)
Statement prepared

+--------+------------+------------+------------+
| ACTION | 2014-09-15 | 2014-09-16 | 2014-09-17 |
+--------+------------+------------+------------+
| ACT1   |          1 |          1 |          1 |
| ACT2   |          0 |          1 |          1 |
| ACT3   |          0 |          0 |          1 |
+--------+------------+------------+------------+
3 rows in set (0.00 sec)

All you have to do is supply the last date and assign it to @dt0;

GIVE IT A TRY !!!