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
Add a table:
CREATE TABLE `dates` (`date` DATE,
UNIQUE KEY `date_idx` (`date`)
);
Insert one row into dates for each day. Now you can use something like this:
SELECT dates.date, COUNT(*) as hits
FROM dates
LEFT JOIN table on dates.date = DATE(table.date_impression)
WHERE dates.date BETWEEN '2005-08-12' AND '2005-08-16'
GROUP BY dates.date;
Populating the dates table initially is a small (one-time) pain. You could
keep it filled with a once-a-day script to insert the current date.
Michael
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
