clear markdown compare help best fiddles feedback dbanow.uk
clear markdown donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 2555247 fiddles created (37455 in the last week).

CREATE TABLE `client` ( `id` int(11) unsigned NOT NULL AUTO_INCREMENT, `name` varchar(128) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 hidden batch(es)


INSERT INTO `client` (`id`, `name`) VALUES (1,'Sony'), (2,'Toshiba'), (3,'Apple'), (4,'LG'), (5,'Uco');
 hidden batch(es)


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;
 hidden batch(es)


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');
 hidden batch(es)


SELECT *, CONCAT(id, '-', RIGHT(DATE(date_start), 5)) AS item FROM event;
id client_id date_start item
1 1 2017-01-12 18:44:42 1-01-12
2 1 2017-01-13 18:44:42 2-01-13
3 1 2017-01-14 18:44:42 3-01-14
4 1 2017-02-12 18:44:42 4-02-12
5 1 2017-03-12 18:44:42 5-03-12
6 1 2017-07-12 18:44:42 6-07-12
7 2 2017-02-12 18:44:42 7-02-12
8 2 2017-03-12 18:44:42 8-03-12
9 2 2017-04-12 18:44:42 9-04-12
10 3 2017-01-12 18:44:42 10-01-12
11 3 2017-01-14 18:44:42 11-01-14
12 3 2017-01-20 18:44:42 12-01-20
13 3 2017-03-12 18:44:42 13-03-12
14 3 2017-05-12 18:44:42 14-05-12
15 3 2017-06-12 18:44:42 15-06-12
16 4 2017-07-12 18:44:42 16-07-12
17 4 2017-07-20 18:44:42 17-07-20
18 5 2017-09-12 18:44:42 18-09-12
19 5 2017-10-12 18:44:42 19-10-12
20 5 2017-03-12 18:44:42 20-03-12
 hidden batch(es)


SELECT client, COUNT(month = 1 OR NULL) AS jan, COUNT(month = 2 OR NULL) AS feb, COUNT(month = 3 OR NULL) AS mar, COUNT(month = 4 OR NULL) AS apr, COUNT(month = 5 OR NULL) AS may, COUNT(month = 6 OR NULL) AS jun, COUNT(month = 7 OR NULL) AS jul, COUNT(month = 8 OR NULL) AS aug, COUNT(month = 9 OR NULL) AS sep, COUNT(month = 10 OR NULL) AS oct, COUNT(month = 11 OR NULL) AS nov, COUNT(month = 12 OR NULL) AS `dec` FROM ( SELECT c.name AS client, MONTH(e.date_start) AS month, CONCAT(e.id, '-', RIGHT(DATE(e.date_start), 5)) AS item FROM event AS e INNER JOIN client AS c ON e.client_id = c.id ) AS derived GROUP BY client WITH ROLLUP ;
client jan feb mar apr may jun jul aug sep oct nov dec
Apple 3 0 1 0 1 1 0 0 0 0 0 0
LG 0 0 0 0 0 0 2 0 0 0 0 0
Sony 3 1 1 0 0 0 1 0 0 0 0 0
Toshiba 0 1 1 1 0 0 0 0 0 0 0 0
Uco 0 0 1 0 0 0 0 0 1 1 0 0
6 2 4 1 1 1 3 0 1 1 0 0
 hidden batch(es)


SELECT client, GROUP_CONCAT(CASE month WHEN 1 THEN item END ORDER BY item ASC SEPARATOR ',') AS jan, GROUP_CONCAT(CASE month WHEN 2 THEN item END ORDER BY item ASC SEPARATOR ',') AS feb, GROUP_CONCAT(CASE month WHEN 3 THEN item END ORDER BY item ASC SEPARATOR ',') AS mar, GROUP_CONCAT(CASE month WHEN 4 THEN item END ORDER BY item ASC SEPARATOR ',') AS apr, GROUP_CONCAT(CASE month WHEN 5 THEN item END ORDER BY item ASC SEPARATOR ',') AS may, GROUP_CONCAT(CASE month WHEN 6 THEN item END ORDER BY item ASC SEPARATOR ',') AS jun, GROUP_CONCAT(CASE month WHEN 7 THEN item END ORDER BY item ASC SEPARATOR ',') AS jul, GROUP_CONCAT(CASE month WHEN 8 THEN item END ORDER BY item ASC SEPARATOR ',') AS aug, GROUP_CONCAT(CASE month WHEN 9 THEN item END ORDER BY item ASC SEPARATOR ',') AS sep, GROUP_CONCAT(CASE month WHEN 10 THEN item END ORDER BY item ASC SEPARATOR ',') AS oct, GROUP_CONCAT(CASE month WHEN 11 THEN item END ORDER BY item ASC SEPARATOR ',') AS nov, GROUP_CONCAT(CASE month WHEN 12 THEN item END ORDER BY item ASC SEPARATOR ',') AS `dec` FROM ( SELECT c.name AS client, MONTH(e.date_start) AS month, CONCAT(e.id, '-', RIGHT(DATE(e.date_start), 5)) AS item FROM event AS e INNER JOIN client AS c ON e.client_id = c.id ) AS derived GROUP BY client WITH ROLLUP ;
client jan feb mar apr may jun jul aug sep oct nov dec
Apple 10-01-12,11-01-14,12-01-20 13-03-12 14-05-12 15-06-12
LG 16-07-12,17-07-20
Sony 1-01-12,2-01-13,3-01-14 4-02-12 5-03-12 6-07-12
Toshiba 7-02-12 8-03-12 9-04-12
Uco 20-03-12 18-09-12 19-10-12
1-01-12,10-01-12,11-01-14,12-01-20,2-01-13,3-01-14 4-02-12,7-02-12 13-03-12,20-03-12,5-03-12,8-03-12 9-04-12 14-05-12 15-06-12 16-07-12,17-07-20,6-07-12 18-09-12 19-10-12
 hidden batch(es)


SELECT client, IF(client IS NULL, COUNT(month = 1 OR NULL), GROUP_CONCAT(CASE month WHEN 1 THEN item END SEPARATOR ',')) AS jan, IF(client IS NULL, COUNT(month = 2 OR NULL), GROUP_CONCAT(CASE month WHEN 2 THEN item END SEPARATOR ',')) AS feb, IF(client IS NULL, COUNT(month = 3 OR NULL), GROUP_CONCAT(CASE month WHEN 3 THEN item END SEPARATOR ',')) AS mar, IF(client IS NULL, COUNT(month = 4 OR NULL), GROUP_CONCAT(CASE month WHEN 4 THEN item END SEPARATOR ',')) AS apr, IF(client IS NULL, COUNT(month = 5 OR NULL), GROUP_CONCAT(CASE month WHEN 5 THEN item END SEPARATOR ',')) AS may, IF(client IS NULL, COUNT(month = 6 OR NULL), GROUP_CONCAT(CASE month WHEN 6 THEN item END SEPARATOR ',')) AS jun, IF(client IS NULL, COUNT(month = 7 OR NULL), GROUP_CONCAT(CASE month WHEN 7 THEN item END SEPARATOR ',')) AS jul, IF(client IS NULL, COUNT(month = 8 OR NULL), GROUP_CONCAT(CASE month WHEN 8 THEN item END SEPARATOR ',')) AS aug, IF(client IS NULL, COUNT(month = 9 OR NULL), GROUP_CONCAT(CASE month WHEN 9 THEN item END SEPARATOR ',')) AS sep, IF(client IS NULL, COUNT(month = 10 OR NULL), GROUP_CONCAT(CASE month WHEN 10 THEN item END SEPARATOR ',')) AS oct, IF(client IS NULL, COUNT(month = 11 OR NULL), GROUP_CONCAT(CASE month WHEN 11 THEN item END SEPARATOR ',')) AS nov, IF(client IS NULL, COUNT(month = 12 OR NULL), GROUP_CONCAT(CASE month WHEN 12 THEN item END SEPARATOR ',')) AS `dec` FROM ( SELECT c.name AS client, MONTH(e.date_start) AS month, CONCAT(e.id, '-', RIGHT(DATE(e.date_start), 5)) AS item FROM event AS e INNER JOIN client AS c ON e.client_id = c.id ) AS derived GROUP BY client WITH ROLLUP ;
client jan feb mar apr may jun jul aug sep oct nov dec
Apple 10-01-12,11-01-14,12-01-20 13-03-12 14-05-12 15-06-12
LG 16-07-12,17-07-20
Sony 1-01-12,2-01-13,3-01-14 4-02-12 5-03-12 6-07-12
Toshiba 7-02-12 8-03-12 9-04-12
Uco 20-03-12 18-09-12 19-10-12
6 2 4 1 1 1 3 0 1 1 0 0
 hidden batch(es)


SELECT client, GROUP_CONCAT(CASE month WHEN 1 THEN item END SEPARATOR ',') AS jan, GROUP_CONCAT(CASE month WHEN 2 THEN item END SEPARATOR ',') AS feb, GROUP_CONCAT(CASE month WHEN 3 THEN item END SEPARATOR ',') AS mar, GROUP_CONCAT(CASE month WHEN 4 THEN item END SEPARATOR ',') AS apr, GROUP_CONCAT(CASE month WHEN 5 THEN item END SEPARATOR ',') AS may, GROUP_CONCAT(CASE month WHEN 6 THEN item END SEPARATOR ',') AS jun, GROUP_CONCAT(CASE month WHEN 7 THEN item END SEPARATOR ',') AS jul, GROUP_CONCAT(CASE month WHEN 8 THEN item END SEPARATOR ',') AS aug, GROUP_CONCAT(CASE month WHEN 9 THEN item END SEPARATOR ',') AS sep, GROUP_CONCAT(CASE month WHEN 10 THEN item END SEPARATOR ',') AS oct, GROUP_CONCAT(CASE month WHEN 11 THEN item END SEPARATOR ',') AS nov, GROUP_CONCAT(CASE month WHEN 12 THEN item END SEPARATOR ',') AS `dec` FROM ( SELECT c.name AS client, MONTH(e.date_start) AS month, CONCAT(e.id, '-', RIGHT(DATE(e.date_start), 5)) AS item FROM event AS e INNER JOIN client AS c ON e.client_id = c.id ) AS derived GROUP BY client UNION ALL SELECT NULL, COUNT(month = 1 OR NULL), COUNT(month = 2 OR NULL), COUNT(month = 3 OR NULL), COUNT(month = 4 OR NULL), COUNT(month = 5 OR NULL), COUNT(month = 6 OR NULL), COUNT(month = 7 OR NULL), COUNT(month = 8 OR NULL), COUNT(month = 9 OR NULL), COUNT(month = 10 OR NULL), COUNT(month = 11 OR NULL), COUNT(month = 12 OR NULL) FROM ( SELECT MONTH(e.date_start) AS month FROM event AS e ) AS derived ;
client jan feb mar apr may jun jul aug sep oct nov dec
Apple 10-01-12,11-01-14,12-01-20 13-03-12 14-05-12 15-06-12
LG 16-07-12,17-07-20
Sony 2-01-13,3-01-14,1-01-12 4-02-12 5-03-12 6-07-12
Toshiba 7-02-12 8-03-12 9-04-12
Uco 20-03-12 18-09-12 19-10-12
6 2 4 1 1 1 3 0 1 1 0 0
 hidden batch(es)


SELECT c.name AS client, GROUP_CONCAT(CASE MONTH(e.date_start) WHEN 1 THEN CONCAT(e.id, '-', RIGHT(DATE(e.date_start), 5)) END SEPARATOR ',') AS jan, GROUP_CONCAT(CASE MONTH(e.date_start) WHEN 2 THEN CONCAT(e.id, '-', RIGHT(DATE(e.date_start), 5)) END SEPARATOR ',') AS feb, GROUP_CONCAT(CASE MONTH(e.date_start) WHEN 3 THEN CONCAT(e.id, '-', RIGHT(DATE(e.date_start), 5)) END SEPARATOR ',') AS mar, GROUP_CONCAT(CASE MONTH(e.date_start) WHEN 4 THEN CONCAT(e.id, '-', RIGHT(DATE(e.date_start), 5)) END SEPARATOR ',') AS apr, GROUP_CONCAT(CASE MONTH(e.date_start) WHEN 5 THEN CONCAT(e.id, '-', RIGHT(DATE(e.date_start), 5)) END SEPARATOR ',') AS may, GROUP_CONCAT(CASE MONTH(e.date_start) WHEN 6 THEN CONCAT(e.id, '-', RIGHT(DATE(e.date_start), 5)) END SEPARATOR ',') AS jun, GROUP_CONCAT(CASE MONTH(e.date_start) WHEN 7 THEN CONCAT(e.id, '-', RIGHT(DATE(e.date_start), 5)) END SEPARATOR ',') AS jul, GROUP_CONCAT(CASE MONTH(e.date_start) WHEN 8 THEN CONCAT(e.id, '-', RIGHT(DATE(e.date_start), 5)) END SEPARATOR ',') AS aug, GROUP_CONCAT(CASE MONTH(e.date_start) WHEN 9 THEN CONCAT(e.id, '-', RIGHT(DATE(e.date_start), 5)) END SEPARATOR ',') AS sep, GROUP_CONCAT(CASE MONTH(e.date_start) WHEN 10 THEN CONCAT(e.id, '-', RIGHT(DATE(e.date_start), 5)) END SEPARATOR ',') AS oct, GROUP_CONCAT(CASE MONTH(e.date_start) WHEN 11 THEN CONCAT(e.id, '-', RIGHT(DATE(e.date_start), 5)) END SEPARATOR ',') AS nov, GROUP_CONCAT(CASE MONTH(e.date_start) WHEN 12 THEN CONCAT(e.id, '-', RIGHT(DATE(e.date_start), 5)) END SEPARATOR ',') AS `dec` FROM event AS e INNER JOIN client AS c ON e.client_id = c.id GROUP BY c.name UNION ALL SELECT NULL, COUNT(MONTH(e.date_start) = 1 OR NULL), COUNT(MONTH(e.date_start) = 2 OR NULL), COUNT(MONTH(e.date_start) = 3 OR NULL), COUNT(MONTH(e.date_start) = 4 OR NULL), COUNT(MONTH(e.date_start) = 5 OR NULL), COUNT(MONTH(e.date_start) = 6 OR NULL), COUNT(MONTH(e.date_start) = 7 OR NULL), COUNT(MONTH(e.date_start) = 8 OR NULL), COUNT(MONTH(e.date_start) = 9 OR NULL), COUNT(MONTH(e.date_start) = 10 OR NULL), COUNT(MONTH(e.date_start) = 11 OR NULL), COUNT(MONTH(e.date_start) = 12 OR NULL) FROM event AS e ;
client jan feb mar apr may jun jul aug sep oct nov dec
Apple 10-01-12,11-01-14,12-01-20 13-03-12 14-05-12 15-06-12
LG 16-07-12,17-07-20
Sony 2-01-13,3-01-14,1-01-12 4-02-12 5-03-12 6-07-12
Toshiba 7-02-12 8-03-12 9-04-12
Uco 20-03-12 18-09-12 19-10-12
6 2 4 1 1 1 3 0 1 1 0 0
 hidden batch(es)