1

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();
forpas
  • 160,666
  • 10
  • 38
  • 76
Abey
  • 69
  • 7

1 Answers1

1

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.

forpas
  • 160,666
  • 10
  • 38
  • 76
  • You sir are a lifesaver :) Thank you very much , it solved the issue. I had a follow up question for the same query. If instead of where strftime('%Y', date) = '2022' "year" I have date range from date and two date like where date between '2022-01-01' and '2022-05-01' how can i modify the query for the same ? – Abey Jul 22 '22 at 04:44
  • @Abey instead of `strftime('%Y', a.date) = '2022'` use `a.date BETWEEN '2022-01-01' AND '2022-05-01'` – forpas Jul 22 '22 at 06:15