MySql Query: include days that have COUNT(id) == 0 but only in the last 30 days

Question

I am doing a query to get the number of builds per day from our database for the last 30 days. But it has become needed to marked days where there were no builds also.

In my WHERE clause I use submittime to determine whether there were builds, how could I modify this to include days that have COUNT(id) == 0 but only in the last 30 days.

Original Query:

   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've 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);

Sounds like the issue is that you don't have the dates in the `builds` table -- take a look at the other `datetime-generation` tagged questions for examples. — OMG Ponies, Jul 27 '11 at 14:12

score 1 · Accepted Answer · answered Jul 27 '11 at 14:31

You need a table of dates, then left join to the builds table.

Something like this:

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);

MySql Query: include days that have COUNT(id) == 0 but only in the last 30 days

1 Answers1

Linked