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