Marcus Bointon <[EMAIL PROTECTED]> wrote on 11/06/2005 05:53:50 PM: > I'm generating data to use for a php graph-drawing utility where I > summarise data into daily counts of events relating to an 'issue' > item from an 'event' table via a 'session' table. My queries are > currently along these lines: > > SELECT DATE_FORMAT(event.timestamp, '%Y-%m-%d') AS adate, COUNT > (event.id) AS eventcount > FROM issue, `session`, event > WHERE `session`.issue_id = issue.id > AND event.session_id = `session`.id > AND DATE_FORMAT(event.timestamp, '%Y-%m-%d') >= '$sdate' > AND DATE_FORMAT(event.timestamp, '%Y-%m-%d') < '$edate' > AND event.eventtype = '$eventtype' > GROUP BY adate > ORDER BY adate > > Given $sdate = '2005-10-01' and $edate = '2005-10-06', I might get > results like: > > 2005-10-01 10 > 2005-10-02 12 > 2005-10-04 8 > 2005-10-05 2 > > These are fine, but notice that days 03 and 06 had 0 results and so > don't appear in the result set at all. I'd like results like this: > > 2005-10-01 10 > 2005-10-02 12 > 2005-10-03 0 > 2005-10-04 8 > 2005-10-05 2 > 2005-10-06 0 > > At present I have a PHP function to pad these gaps in date ranges > with zero values, but I suspect I could be getting MySQL to do this. > How? > > I could have a table containing all possible dates and do a left join > with that, but that just seems like a crap idea! Perhaps create a set > of fixed values for GROUP BY? > > Marcus > -- > Marcus Bointon > Synchromedia Limited: Putting you in the picture > [EMAIL PROTECTED] | http://www.synchromedia.co.uk > >
Both methods you describe are the commonly used techniques to solve your particular problem. Every RDBMS system I have used responds in exactly the same way to your query. A) a database should not respond with data it does not have. B) I don't know of a SQL statement (in any SQL dialect) that auto-generates a list of dates (or any other series of values) that you could use in this situation. Many SQL languages have looping constructs but I do not think that is what you are looking for. I think you were hoping for a single function/modifier that would have caused the missing dates to appear. AFAIK, SQL does not have such a modifier (not just MySQL but any SQL-based RDBMS) You suggest creating a "set of values" for the GROUP BY statement... how is that functionally different than using another table and LEFT joining? Shawn Green Database Administrator Unimin Corporation - Spruce Pine
