Dan Tappin wrote:
> I have a table full of data... a log of sorts. Each row has a
> timestamp.
>
> I want to generate some reports based on this data.
>
> For example I want a COUNT(*) of the rows for each day for the past
> week, 30 days, 12 months etc.
>
> I have no problem generating the query but I am stuck on a creative
> way to deal with the periods with no data. For example:
>
> SELECT COUNT(*) as hits,
> DATE(date_impression) as date
> FROM
> table
> GROUP BY date
>
> +-----------------------+---------------------+
> |hits |date |
> +-----------------------+---------------------+
> | 39 | 2005-08-12 |
> | 27 | 2005-08-13 |
> | 38 | 2005-08-15 |
> | 28 | 2005-08-16 |
> +-----------------------+---------------------+
>
> Now the problem is that there could be days with no data (the 14th in
> my example). Ideally I want to show the last 7 days (or what ever
> period I want) and show the COUNT(*) including the days with no data
> like...
>
> +-----------------------+---------------------+
> |hits |date |
> +-----------------------+---------------------+
> | 39 | 2005-08-12 |
> | 27 | 2005-08-13 |
> | 0 | 2005-08-14 |
> | 38 | 2005-08-15 |
> | 28 | 2005-08-16 |
> +-----------------------+---------------------+
>
> Now I can manipulate the data afterwards (i.e. look for empty rows
> via PHP where this is going to end up...) but it would be much easier
> to get the data direct from MySQL. Is there any (easy) way to do
> this in MySQL?
>
> Dan T
>
Not that I know of.
When I have to do things like this, I write a script which does a
separate query per day. Unfortunately PHP's date functions are pretty
horrible, so I also use mysql as a calculator to find the next day in my
loop. This is terribly inefficient, but very easy, and our server is
high powered and under no load :) Maybe someone has a better idea though.
--
Daniel Kasak
IT Developer
NUS Consulting Group
Level 5, 77 Pacific Highway
North Sydney, NSW, Australia 2060
T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989
email: dkasak@stripped
website: http://www.nusconsulting.com.au
