How to fill GROUP_CONCAT() with 0's when JOIN data missing

Question

I'm using MySQL 8.0's date generation and am joining in data when/where available. This technique allows me to ensure this query returns zero values. This works perfectly when I'm pulling data for a single stat. Now I'm trying to adjust it to pull multiple stats at the same time so I can generate reports pretty easily.

Here's the current output:

[2019年01月24日] 0
[2019年01月25日] 0
[2019年01月26日] 0
[2019年01月27日] 62
[2019年01月28日] 64,22,7
[2019年01月29日] 65,21,7
[2019年01月30日] 66,21

My objective would be to adjust this query so that any specific stat that doesn't have an entry gets filled with zeros so the ideal output would look like:

[2019年01月24日] 0,0,0
[2019年01月25日] 0,0,0
[2019年01月26日] 0,0,0
[2019年01月27日] 62,0,0
[2019年01月28日] 64,0,7
[2019年01月29日] 65,21,7
[2019年01月30日] 66,21,0

 WITH RECURSIVE dates (date) AS 
 (
 SELECT :startingDate
 UNION ALL
 SELECT date + INTERVAL 1 DAY FROM dates WHERE date <= DATE_SUB(:endingDate, INTERVAL 1 DAY)
 )
 SELECT
 COALESCE(daily_stats.date, dates.date) AS label,
 GROUP_CONCAT(COALESCE(daily_stats.value, 0) ORDER BY FIELD(stat, 132, 120, 111)) AS value
 FROM dates
 LEFT JOIN daily_stats ON stat IN(132, 120, 111) AND daily_stats.date = dates.date
 GROUP BY label;

I'm unsure how to accomplish this without doing a UNION on several select queries. Is there a more efficient approach?

My table:

CREATE TABLE `daily_stats` (
 `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
 `date` date NOT NULL,
 `stat` int(11) NOT NULL,
 `value` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
 `created_at` timestamp NULL DEFAULT NULL,
 `updated_at` timestamp NULL DEFAULT NULL,
 PRIMARY KEY (`id`),
 UNIQUE KEY `daily_stats_date_stat_unique` (`date`,`stat`),
) ENGINE=InnoDB AUTO_INCREMENT=4412 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

• danblack: As daily_stats.value is a VARCHAR and not an integer type (like 0) perhaps GROUP_CONCAT(COALESCE(daily_stats.value, "0")) or GROUP_CONCAT(IFNULL(daily_stats.value, "0"))

Answer 1

Both date and stat may be absent. So you must generate not date array, but (date, stat) pairs array:

WITH RECURSIVE dates (date) AS 
(
 SELECT '2019-01-24'
 UNION ALL
 SELECT date + INTERVAL 1 DAY FROM dates WHERE date <= DATE_SUB('2019-01-30', INTERVAL 1 DAY)
),
stats (stat) AS (
 SELECT DISTINCT stat 
 FROM daily_stats
 WHERE stat IN(132, 120, 111)
), 
dates_stats (date, stat) AS
(
 SELECT date, stat
 FROM dates, stats
)
SELECT
 dates_stats.date AS label,
 GROUP_CONCAT(COALESCE(daily_stats.value, 0) ORDER BY FIELD(dates_stats.stat, 132, 120, 111)) AS value
FROM dates_stats
LEFT JOIN daily_stats ON daily_stats.stat IN(132, 120, 111) 
 AND daily_stats.date = dates_stats.date 
 AND daily_stats.stat = dates_stats.stat
GROUP BY label;

fiddle

Or maybe ever

stats (stat) AS ( SELECT 132 stat
 UNION ALL
 SELECT 120
 UNION ALL
 SELECT 111
), 

Answer 2

Try this variation:

WITH RECURSIVE
dates (date) AS 
 (
 SELECT CAST('2019-01-24' AS date)
 UNION ALL
 SELECT date + INTERVAL 1 DAY
 FROM dates
 WHERE date <= DATE_SUB('2019-01-30', INTERVAL 1 DAY)
 ),
s (ord, stat) AS
 ( SELECT 1, 132 UNION ALL
 SELECT 2, 120 UNION ALL
 SELECT 3, 111
 )
SELECT
 dates.date AS label,
 GROUP_CONCAT(COALESCE(ds.value, '0') ORDER BY s.ord) AS value
FROM dates
CROSS JOIN s
LEFT JOIN daily_stats AS ds
 ON ds.stat = s.stat
 AND ds.date = dates.date
GROUP BY dates.date ;

Test at dbfiddle.uk