I have the following mysql query statement:
SELECT COUNT(*) as ttl_rows FROM (SELECT DISTINCT(date(timeentry)) AS day, COUNT(*) AS total FROM history where MONTH(CAST(timeentry as date)) = MONTH(NOW()) AND YEAR(CAST(timeentry as date)) = YEAR(NOW()) GROUP BY DATE_FORMAT(timeentry, '%d') ASC) as ttl_row
the statement performs a search for all records entered for the current month and thus would produced the following:
day total 2012-12-01 1 2012-12-02 6 2012-12-04 4 2012-12-05 3
As you can see, there is no record for 2012-12-03. My interest here is to modify the query to return all dates with the date range and where there is no entry, insert 0. So essentially, my return would be something like:
day total 2012-12-01 1 2012-12-02 6 **2012-12-03 0** 2012-12-04 4 2012-12-05 3
I hope this is achievable and that anyone here may be able to assist.
Edited by mbarandao