List:General Discussion« Previous MessageNext Message »
From:Michael Stassen Date:August 17 2005 4:26am
Subject:Re: SELECT question - query for records over a series of dates
View as plain text  
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
Thread
SELECT question - query for records over a series of datesDan Tappin17 Aug
  • Re: SELECT question - query for records over a series of datesDaniel Kasak17 Aug
  • Re: SELECT question - query for records over a series of datesMichael Stassen17 Aug
    • Re: SELECT question - query for records over a series of datesDan Tappin17 Aug
      • Re: SELECT question - query for records over a series of datesMichael Stassen18 Aug