Count rows by day (TIMESTAMP) but show zero count days?

Hi guys,

I have this MySQL query:

SELECT DATE(FROM_UNIXTIME(submitted)) day, COUNT(*) count FROM table GROUP BY day ORDER BY day ASC

It works well and counts the rows by day using each rows timestamp. However, I'd like it to count zero on days without any rows. At the moment it gives:

day              |  count

-----------------------------

2011-02-16  |  5

2011-02-17  |  10

2011-02-19  |  2

2011-02-20  |  1

2011-02-21  |  4

2011-02-22  |  2

2011-02-23  |  1

2011-02-24  |  3

I'd like:

day              |  count

-----------------------------

2011-02-16  |  5

2011-02-17  |  10

2011-02-18  |  0 <<< Possible to include these zero count days?

2011-02-19  |  2

2011-02-20  |  1

2011-02-21  |  4

2011-02-22  |  2

2011-02-23  |  1

2011-02-24  |  3

Thanks for the help guys!

Hi

It is possible, but to do it you need a list of dates. You can do that by having a tables of numbers and adding that to the start date.

Something like this:-

SELECT Y.day, COUNT( Z.id )
FROM (SELECT DATE_ADD( '2011-01-01', INTERVAL a.i + b.i *10 + c.i *100
DAY ) AS DAY
FROM integers a
CROSS JOIN integers b
CROSS JOIN integers c
HAVING DAY BETWEEN '2011-01-15' AND '2011-04-02' ) Y
LEFT OUTER JOIN sometable2 Z ON Y.day = DATE( Z.sometime )
GROUP BY Y.DAY
ORDER BY Y.DAY ASC

This is using a table called integers which has a single column called i with 10 rows with i having the values 0 to 9. Joining this against itself gives a range of numbers, and this can be added to a start date. Use HAVING to get the date range you care about and JOIN that with the table you are trying to count the daily records from.

All the best

Keith

It's always bothered me that you can't use a derived temporary table.

Thanks kickstart, I got the query working nicely with timestamps and managed to use it successfully with pChart to plot data by date on a nice graph.

Thanks a lot,

RJP