Marcus Bointon <[EMAIL PROTECTED]> wrote on 11/07/2005 03:16:08 AM: > On 7 Nov 2005, at 01:06, [EMAIL PROTECTED] wrote: > > > A) a database should not respond with data it does not have. > > Well, it's not really - it's returning a count of 0 for a particular > match condition. I could achieve the same result by saying: > > 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') >= '2005-10-01' > AND event.eventtype = '$eventtype' > > and repeating it for each date I want to know about. But I'm sure it > can be done in one query somehow.
You are correct in that you can do this in one query. However in order to do so, the database must have a set of date values to group on. Even with your suggested extension to the GROUP BY clause you are providing a list of values. That means that the data is user-provided not table-stored. > > > You suggest creating a "set of values" for the GROUP BY > > statement... how > > is that functionally different than using another table and LEFT > > joining? > > Because the set of values could be generated dynamically and passed > in the query and thus would not have to be present in the DB. I'm > thinking along the lines of: > > GROUP BY FIELD(DATE_FORMAT(event.timestamp, '%Y-%m-%d'), > '2005-10-01','2005-10-02','2005-10-03','2005-10-04','2005-10-05','2005-1 > 0-06') > > That syntax is lifted from ORDER BY, is something similar available > for GROUP BY? > I am sorry but no, neither that syntax nor anything like is part of MySQL. Each term you "group by" in a GROUP BY clause is another "layer" of grouping. I understand the intent of your syntax but I can't think of a way to implement it (not enough coffee, yet). Nothing is stopping you from making a SERIES() modifier for the GROUP BY clause (like DISTINCT for SELECT) as the code is completely open source. Harold (in another response) described a function to simulate making a list table available in PostgreSQL, perhaps you could port that to MySQL as an alternative? The secret to having anything show up in a query with a GROUP BY clause is to have it show up in the same query without the GROUP BY clause. If it's not part of the data, it can't be part of the answer unless it is provided from somewhere else. > Marcus > -- > Marcus Bointon > Synchromedia Limited: Putting you in the picture > [EMAIL PROTECTED] | http://www.synchromedia.co.uk > Shawn Green Database Administrator Unimin Corporation - Spruce Pine
