Query monthly having group_concat and group by need help

4 votes
2 answers
4521 views
mysql group-by pivot group-concatenation
                          I’m stuck on a join tables query presenting data monthly involving GROUP BY and GROUP_CONCAT.  

Here’s a simple client table (DDL and DML at the bottom of this post):

    id | Name
    1  | Sony
    2  | Toshiba
    3  | Apple
    4  | LG
    5  | Uco


Then the event table


    id | client_id | date_start
    1 | 1 | 2017-01-12 18:44:42
    2 | 1 | 2017-01-13 18:44:42
    3 | 1 | 2017-01-14 18:44:42
    4 | 1 | 2017-02-12 18:44:42
    5 | 1 | 2017-03-12 18:44:42
    6 | 1 | 2017-07-12 18:44:42
    7 | 2 | 2017-02-12 18:44:42
    8 | 2 | 2017-03-12 18:44:42
    9 | 2 | 2017-04-12 18:44:42
    10 | 3 | 2017-01-12 18:44:42
    11 | 3 | 2017-01-14 18:44:42
    12 | 3 | 2017-01-20 18:44:42
    13 | 3 | 2017-03-12 18:44:42
    14 | 3 | 2017-05-12 18:44:42
    15 | 3 | 2017-06-12 18:44:42
    16 | 4 | 2017-07-12 18:44:42
    17 | 4 | 2017-07-20 18:44:42
    18 | 5 | 2017-09-12 18:44:42
    19 | 5 | 2017-10-12 18:44:42
    20 | 5 | 2017-03-12 18:44:42


The desired result is as follows. The string number, for example (10-01-12) on Jan/Apple is formatted as id-month-day. 


  

What I’ve done so far is using case when to split result monthly: 

    select * from (
    select e.id, c.name as client,
    (CASE WHEN MONTH(e.date_start) = 1 then GROUP_CONCAT(CONCAT(e.id,'-',LPAD(month(date_start),2,'0'), '-', LPAD(day(date_start),2,'0')) SEPARATOR ',')END) as jan,
    (CASE WHEN MONTH(e.date_start) = 2 then GROUP_CONCAT(CONCAT(e.id,'-',LPAD(month(date_start),2,'0'), '-', LPAD(day(date_start),2,'0')) SEPARATOR ',')END) as feb,
    (CASE WHEN MONTH(e.date_start) = 3 then GROUP_CONCAT(CONCAT(e.id,'-',LPAD(month(date_start),2,'0'), '-', LPAD(day(date_start),2,'0')) SEPARATOR ',')END) as mar,
    (CASE WHEN MONTH(e.date_start) = 4 then GROUP_CONCAT(CONCAT(e.id,'-',LPAD(month(date_start),2,'0'), '-', LPAD(day(date_start),2,'0')) SEPARATOR ',')END) as apr,
    (CASE WHEN MONTH(e.date_start) = 5 then GROUP_CONCAT(CONCAT(e.id,'-',LPAD(month(date_start),2,'0'), '-', LPAD(day(date_start),2,'0')) SEPARATOR ',')END) as may,
    (CASE WHEN MONTH(e.date_start) = 6 then GROUP_CONCAT(CONCAT(e.id,'-',LPAD(month(date_start),2,'0'), '-', LPAD(day(date_start),2,'0')) SEPARATOR ',')END) as jun,
    (CASE WHEN MONTH(e.date_start) = 7 then GROUP_CONCAT(CONCAT(e.id,'-',LPAD(month(date_start),2,'0'), '-', LPAD(day(date_start),2,'0')) SEPARATOR ',')END) as jul,
    (CASE WHEN MONTH(e.date_start) = 8 then GROUP_CONCAT(CONCAT(e.id,'-',LPAD(month(date_start),2,'0'), '-', LPAD(day(date_start),2,'0')) SEPARATOR ',')END) as aug,
    (CASE WHEN MONTH(e.date_start) = 9 then GROUP_CONCAT(CONCAT(e.id,'-',LPAD(month(date_start),2,'0'), '-', LPAD(day(date_start),2,'0')) SEPARATOR ',')END) as sep,
    (CASE WHEN MONTH(e.date_start) = 10 then GROUP_CONCAT(CONCAT(e.id,'-',LPAD(month(date_start),2,'0'), '-', LPAD(day(date_start),2,'0')) SEPARATOR ',')END) as oct,
    (CASE WHEN MONTH(e.date_start) = 11 then GROUP_CONCAT(CONCAT(e.id,'-',LPAD(month(date_start),2,'0'), '-', LPAD(day(date_start),2,'0')) SEPARATOR ',')END) as nov,
    (CASE WHEN MONTH(e.date_start) = 12 then GROUP_CONCAT(CONCAT(e.id,'-',LPAD(month(date_start),2,'0'), '-', LPAD(day(date_start),2,'0')) SEPARATOR ',')END) as dec 
      
    from 
     event as e 
     left join client as c on c.id=e.client_id 
    group by month(date_start),client
    order by client
    ) t 





But query above need final touch to group by client. How do I concat having group by client result as appear on the desired table above with comma as separator?

The second part is counting the sum of each data monthly. Not as important, I really need to get part one to work. 


Here is the SQL for data and tables. 


    CREATE TABLE client (
      id int(11) unsigned NOT NULL AUTO_INCREMENT,
      name varchar(128) DEFAULT NULL,
      PRIMARY KEY (id)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    
    LOCK TABLES client WRITE;

    INSERT INTO client (id, name)
    VALUES
    	(1,'Sony'),
    	(2,'Toshiba'),
    	(3,'Apple'),
    	(4,'LG'),
    	(5,'Uco');
    
    
    UNLOCK TABLES;
    
    CREATE TABLE event (
      id int(11) unsigned NOT NULL AUTO_INCREMENT,
      client_id int(11) unsigned DEFAULT NULL,
      date_start timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
      PRIMARY KEY (id),
      KEY client_id (client_id),
      KEY date_start (date_start)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
    
    LOCK TABLES event WRITE;
    
    
    INSERT INTO event (id, client_id, date_start)
    VALUES
    	(1,1,'2017-01-12 18:44:42'),
    	(2,1,'2017-01-13 18:44:42'),
    	(3,1,'2017-01-14 18:44:42'),
    	(4,1,'2017-02-12 18:44:42'),
    	(5,1,'2017-03-12 18:44:42'),
    	(6,1,'2017-07-12 18:44:42'),
    	(7,2,'2017-02-12 18:44:42'),
    	(8,2,'2017-03-12 18:44:42'),
    	(9,2,'2017-04-12 18:44:42'),
    	(10,3,'2017-01-12 18:44:42'),
    	(11,3,'2017-01-14 18:44:42'),
    	(12,3,'2017-01-20 18:44:42'),
    	(13,3,'2017-03-12 18:44:42'),
    	(14,3,'2017-05-12 18:44:42'),
    	(15,3,'2017-06-12 18:44:42'),
    	(16,4,'2017-07-12 18:44:42'),
    	(17,4,'2017-07-20 18:44:42'),
    	(18,5,'2017-09-12 18:44:42'),
    	(19,5,'2017-10-12 18:44:42'),
    	(20,5,'2017-03-12 18:44:42');
    
    UNLOCK TABLES;







                        
Asked by Appschema (43 rep)
Aug 16, 2017, 01:25 PM
Last activity: Apr 10, 2021, 06:42 PM
Query monthly having group_concat and group by need help

Related Questions
