MySQL 8.0 – How to Fill GROUP_CONCAT() with 0’s When JOIN Data is Missing

mysql-8.0

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;

Best Answer

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
),
Related Question