Return count of rows for all month, even though there might not be an entry for the month. Return 0 for month/d with no entry

Question

I am trying to get month count even if there isn't a entry for a month. I have looked at few examples and failed to make it work. I have a 3 tables as below

$tablename="appointments"

timeIn	status
2020-10-25 13:00:00	Completed
2020-11-25 13:00:00	Completed
2020-12-25 13:00:00	Completed

I am able to get the following count for each month as below by using the following select statement:

select MONTHNAME(timeIn),Count(*) from ".$tablename." WHERE timeIn!=:null group by MONTHNAME(timeIn) order by MONTHNAME(timeIn) DESC"

{October:"1", November: "1", December: "1"}

By searching online i found this select which i have modified to work with my table. It is not working and i am not sure what is wrong with this as i am not well verse with mysql.

SELECT  Months.id AS `month` , COUNT(`".$tablename."`.timeIn) AS `count` FROM (SELECT 'January' as ID UNION SELECT 'February' as ID UNION  SELECT 'March' as ID UNION SELECT 'April' as ID UNION SELECT 'May' as ID UNION SELECT 'June' as ID UNION SELECT 'July' as ID UNION SELECT 'August' as ID UNION SELECT 'September' as ID UNION SELECT 'October' as ID UNION SELECT 'November' as ID UNION SELECT 'December' as ID) as Months LEFT JOIN `".$tablename."` on Months.id=monthname(`".$tablename."`.timeIn) AND (status = 'Completed') GROUP BY Months.

I would like to get results as below, where it would return 0 if there is no entry for the month. Please be able to provide some explanation as it is a bit challenging to understand mysql statement. Appreciate any help i can get

{January: "0",.....,November:"0",October:"1", November: "1", December: "1"}

UPDATE FROM AKINA's ANSWER

$sql="SELECT MONTHNAME(CONCAT('2020-', cte.num, '-1')) `Month`, COUNT(".$tablename.".timeIn) `Count` FROM (SELECT 1 NUM UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10 UNION SELECT 11 UNION SELECT 12) cte LEFT JOIN ".$tablename." ON cte.num = MONTH(".$tablename.".timeIn) GROUP BY 1;";
$data=$con->prepare($sql);
$data->execute();
$rows=$data->fetchAll(PDO::FETCH_ASSOC);

Just handle the missing month problem in your application code — Strawberry

Akina Akina · Accepted Answer · 2020-12-12T06:16:24

WITH RECURSIVE
cte AS ( SELECT 1 num
         UNION ALL
         SELECT num + 1 FROM cte WHERE num < 12 )
SELECT MONTHNAME(CONCAT('2020-', cte.num, '-1')) `Month`, 
       COUNT(appointments.timeIn) `Count`
FROM cte
LEFT JOIN appointments ON cte.num = MONTH(appointments.timeIn)
GROUP BY 1;

fiddle

PS. MySQL 8 needed.

working with version 5.6.32 – Ahsan

SELECT MONTHNAME(CONCAT('2020-', cte.num, '-1')) `Month`, COUNT(appointments.timeIn) `Count`
FROM (SELECT 1 NUM UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION 
      SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION 
      SELECT 9 UNION SELECT 10 UNION SELECT 11 UNION SELECT 12) cte
LEFT JOIN appointments ON cte.num = MONTH(appointments.timeIn)
GROUP BY 1;

fiddle

Return count of rows for all month, even though there might not be an entry for the month. Return 0 for month/d with no entry

1 Answers