MySql Query: includes days with COUNT (id) == 0, but only in the last 30 days

Question

I make a request to get the number of builds per day from our database for the last 30 days. But this became necessary on the marked days, when there were also no assemblies.

In my WHERE clause, I use submittime to determine if there were assemblies, how can I change this to include days with COUNT (id) == 0, but only in the last 30 days.

Original request:

SELECT COUNT(id) AS 'Past-Month-Builds', CONCAT(MONTH(submittime), '-', DAY(submittime)) as 'Month-Day' FROM builds WHERE DATE(submittime) >= DATE_SUB(CURDATE(), INTERVAL 30 day) GROUP BY MONTH(submittime), DAY(submittime); 

What I tried:

  SELECT COUNT(id) AS 'Past-Month-Builds', CONCAT(MONTH(submittime), '-', DAY(submittime)) as 'Month-Day' FROM builds WHERE DATE(submittime) >= DATE_SUB(CURDATE(), INTERVAL 30 day) OR COUNT(id) = 0 GROUP BY MONTH(submittime), DAY(submittime); 

Answer 1

You need a date table, and then a left link to the build table.

Something like that:

 SELECT COUNT(id) AS 'Past-Month-Builds', CONCAT(MONTH(DateTable.Date), '-', DAY(DateTable.Date)) as 'Month-Day' FROM DateTable LEFT JOIN builds ON DATE(builds.submittime) = DateTable.Date WHERE DateTable.Date >= DATE_SUB(CURDATE(), INTERVAL 30 day) GROUP BY MONTH(submittime), DAY(submittime);