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: [EMAIL PROTECTED]
website: http://www.nusconsulting.com.au
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
