How to replace null with 0 in GROUP

I couldn't apply this question as my database structure is different.

My appointment table has dates in yyyy-mm-dd format and status of appointments as pending, cancelled and completed. My query generates comma separated results for 12 months :

$pending = $con->query("SELECT GROUP_CONCAT(cnt) cnt
FROM (select count(*) cnt from appointment where strftime('%Y', date) = '2022' and status like '%PENDING%' GROUP BY strftime('%m', date)) q;")->fetchColumn();

There are no dates for some months, I want 0 in those result sets. All months except February and April have 1 appointment and I would like the result to be like 1,0,1,0,1,1,1,1,1,1,1,1.

I tried this but it didn't help :

$pending = $con->query("SELECT coalesce(GROUP_CONCAT(cnt),0) cnt
    FROM (select count(*) cnt from appointment where strftime('%Y', date) = '2022' and status like '%PENDING%' GROUP BY strftime('%m', date)) q;")->fetchColumn();

CodePudding user response：

Use a CTE that returns all the month numbers 01 to 12 and do a LEFT join to the table:

WITH months(month) AS (VALUES ('01'), ('02'), ('03'), ('04'), ('05'), ('06'), ('07'), ('08'), ('09'), ('10'), ('11'), ('12'))
SELECT DISTINCT GROUP_CONCAT(COUNT(a.date)) OVER (
                  ORDER BY m.month 
                  ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
                ) result 
FROM months m LEFT JOIN appointment a
ON strftime('%m', a.date) = m.month AND strftime('%Y', a.date) = '2022' AND a.status LIKE '%PENDING%' 
GROUP BY m.month;

Instead of GROUP_CONCAT() aggregate function, which does not support an ORDER BY clause, I use GROUP_CONCAT() window function.

See a simplified demo.