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