MySQL: Select All Dates In a Range Even If No Records Present

Question

I have a database of users. I would like to create a graph based on userbase growth. The query I have now is:

SELECT DATE(datecreated), count(*) AS number FROM users 
WHERE DATE(datecreated) > '2009-06-21' AND DATE(datecreated) <= DATE(NOW())
GROUP BY DATE(datecreated) ORDER BY datecreated ASC

This returns almost what I want. If we get 0 users one day, that day is not returned as a 0 value, it is just skipped and the next day that has at least one user is returned. How can I get something like (psuedo-response):

date1 5
date2 8
date3 0
date4 0
date5 9
etc...

where the dates with zero show up in sequential order with the rest of the dates?

Thanks!

Answer 1

This question asks the same thing I think. Generally the accepted answer seems to be that you either do it in your application logic (read in what you have into an array, then loop through the array and create the missing dates), or you use temporary tables filled with the dates you wish to join.

Answer 2

Do a right outer join to a table, call it tblCalendar, that is pre-populated with the dates you wish to report on. And join on the date field.

Paul

Answer 3

On further thought... something like this should be what you want:

CREATE TEMPORARY TABLE DateSummary1 ( datenew timestamp ) SELECT DISTINCT(DATE(datecreated)) as datenew FROM users;

CREATE TEMPORARY TABLE DateSummary2 ( datenew timestamp, number int ) SELECT DATE(datecreated) as datenew, count(*) AS number FROM users WHERE DATE(datecreated) > '2009-06-21' AND DATE(datecreated) <= DATE(NOW()) GROUP BY DATE(datecreated) ORDER BY datecreated ASC;

SELECT ds1.datenew,ds2.number FROM DateSummary1 ds1 LEFT JOIN DateSummary2 ds2 on ds1.datenew=ds2.datenew;

This gives you all the dates in the first table, and the "count" summary data in the second table... might need to replace "ds2.number" with "IF(ISNULL(ds2.number),0,ds2.number)" or something similar.