Creating a pivot table with changing dates in MySQL

Question

So I am trying to create a pivot table with a SQL query. Currently I am doing this process in sheets but would like to make it a bit less painful.

ip    domain      type         date      provider type_of_event number_of_events
999  mail.com    normal      2018-08-31  orange      abuse             12
999  mail1.com    normal     2018-08-30  orange      abuse             11
999  mail1.com    normal     2018-08-29  orange      abuse             13
999  mail.com    normal      2018-08-31  orange      abuse             10

And I would like it to be like this

 Domain         2018-08-29  2018-08-30  2018-08-31  Grand Total
mail.com                                 22          22
mail1.com             13        11                   24

I am having a problem with the formatting as most examples I am finding have stationary fields and then dates are going to always change but I need it to display the current field that that is being worked on. Which will always be in 3 day increments. I need to it also reference the type_of_event as the example only shows one type but I have multiple in this same database.

This question is different from others as I do not have multiple tables this data is coming from. All of this data is in one table. I dont see how a pivot join is going to help when I have nothing to join to?

So Using this

SELECT CONCAT('SELECT
domain,
SUM(number_of_events * (date_of_event = CURRENT_DATE() - INTERVAL 3 DAY))
  AS `', CURRENT_DATE() - INTERVAL 3 DAY, '`,
SUM(number_of_events * (date_of_event = CURRENT_DATE() - INTERVAL 2 DAY))
  AS `', CURRENT_DATE() - INTERVAL 2 DAY, '`,
SUM(number_of_events * (date_of_event = CURRENT_DATE() - INTERVAL 1 DAY))
  AS `', CURRENT_DATE() - INTERVAL 1 DAY, '`,
SUM(number_of_events) AS `Grand Total`
FROM signal_spam
GROUP BY domain;'
) AS theQuery;

Gives me

+---------------------------------------------------------------------------- 
----------------------------------------------------------------------------- 
- 
----------------------------------------------------------------------------- 
- 
----------------------------------------------------------------------------- 
- 
----------------------------------------------------------------------------- 
- 
----------------+
| theQuery                                                                                                                                                                                                                                                                                                                                                                                                           
|
+---------------------------------------------------------------------------- 
----------------------------------------------------------------------------- 
- 
------------------------------------------------------------------------------ 
------------------------------------------------------------------------------ 
------------------------------------------------------------------------------ 
----------------+
| SELECT
domain,
SUM(number_of_events * (date_of_event = CURRENT_DATE() - INTERVAL 3 DAY))
  AS `2018-09-11`,
SUM(number_of_events * (date_of_event = CURRENT_DATE() - INTERVAL 2 DAY))
  AS `2018-09-12`,
SUM(number_of_events * (date_of_event = CURRENT_DATE() - INTERVAL 1 DAY))
  AS `2018-09-13`,
SUM(number_of_events) AS `Grand Total`
FROM signal_spam
GROUP BY domain; |
+---------------------------------------------------------------------------- 
----------------------------------------------------------------------------- 
- 
 ---------------------------------------------------------------------------- 
-- 
----------------------------------------------------------------------------- 
- 
----------------------------------------------------------------------------- 
- 
 ----------------+
1 row in set (0.00 sec)

The if I remove the concat I get no counts in the rows just the grand total and no date is listed either

+------------------------------------+--------------------------------------- 
+---------------------------------------+------------------------------------ 
- 
--+-------------+
| domain                             | ', CURRENT_DATE() - INTERVAL 3 DAY, ' 
| 
', CURRENT_DATE() - INTERVAL 2 DAY, ' | ', CURRENT_DATE() - INTERVAL 1 DAY, ' 
| Grand Total |
+------------------------------------+--------------------------------------- 
+---------------------------------------+------------------------------------ 
- 
--+-------------+
| 0.pool.         |                                     0 
|                                     
0 |                                     0 |         258 |
| 143.com               |                                     0 
|                                     
0 |                                     0 |           2 |

Report it run two times a week. Mondays which covers Sun,Sat, Friday and then I run it Thursday for Monday, Tuesday, Wednesday — James Madison, Sep 14 '18 at 18:56
You have not specified how you want to reference the type_of_event. Do you want separate columns for each event type? Separate rows? — Scott Noyes, Sep 14 '18 at 19:27
I can sort each type of event. Ultimately this will be a php type job that have a variable got both. So just looking for the format — James Madison, Sep 14 '18 at 20:25

Scott Noyes · Answer 1 · 2018-09-14T21:20:03.437

Dynamic pivot tables in MySQL are typically done by writing a query which then generates a string of SQL.

SELECT CONCAT('
  SELECT
    domain,
    SUM(number_of_events * (date = CURRENT_DATE() - INTERVAL 3 DAY))
      AS `', CURRENT_DATE() - INTERVAL 3 DAY, '`,
    SUM(number_of_events * (date = CURRENT_DATE() - INTERVAL 2 DAY))
      AS `', CURRENT_DATE() - INTERVAL 2 DAY, '`,
    SUM(number_of_events * (date = CURRENT_DATE() - INTERVAL 1 DAY))
      AS `', CURRENT_DATE() - INTERVAL 1 DAY, '`,
    SUM(number_of_events) AS `Grand Total`
  FROM tableName
  GROUP BY domain;'
) AS theQuery;

Take the result of that query, and send it back in to MySQL.

For example, running that query today produces:

SELECT
    domain,
    SUM(number_of_events * (date = CURRENT_DATE() - INTERVAL 3 DAY))
      AS `2018-09-11`,
    SUM(number_of_events * (date = CURRENT_DATE() - INTERVAL 2 DAY))
      AS `2018-09-12`,
    SUM(number_of_events * (date = CURRENT_DATE() - INTERVAL 1 DAY))
      AS `2018-09-13`,
    SUM(number_of_events) AS `Grand Total`
  FROM signal_spam
  GROUP BY domain;

There's the pivot table query, dynamically generated by MySQL. Now just copy/paste that query back into the client, and the final result:

+-----------+------------+------------+------------+-------------+
| domain    | 2018-09-11 | 2018-09-12 | 2018-09-13 | Grand Total |
+-----------+------------+------------+------------+-------------+
| mail.com  |          0 |          0 |         22 |          22 |
| mail1.com |         13 |         11 |          0 |          24 |
+-----------+------------+------------+------------+-------------+

not sure as to what you mean with sending it back in. the above game me — James Madison, Sep 14 '18 at 20:36

Creating a pivot table with changing dates in MySQL

1 Answers1