Couldn't you also group by grouper2 and date2 ? Something like: ... GROUP BY grouper1, grouper2, date2
Just a hint.... I didn't test it. On Thu, 2003-10-16 at 01:01, Travis Reeder wrote: > I am trying to do an aggregate query, but having some problems and here > they are simplified: > > Table1 > date1 > grouper1 > x > > Table2 > date2 > grouper2 > y > > Query is something like this: > > SELECT SUM(x) as sum_x, SUM(y) as sum_y > FROM Table1 > LEFT OUTER JOIN Table2 on grouper2 = grouper1 > (date2 >= '2003-07-01 00:00:00' AND date2 <= '2003-07-01 23:59:59' ) > and year(date2) = year(date1) > and month(date2) = month(date1) > and DAYOFMONTH(date2) = DAYOFMONTH(date1) > ) > WHERE date1 >= '2003-07-01 00:00:00' AND date2 <= '2003-07-01 23:59:59' > GROUP BY grouper1 > > > Ok, so this works fine unless one of the following happens: > 1. there are 2 entries in Table2 that have the same dayofmonth and > grouper2 value. In this case, they are joined to the same row in Table1 > so I have a duplicate of a record in Table1 and sum_x will be double for > that particular day. > > 2. The exact opposite, so 2 entreies in table1 on the same day, i'm not > too worried about this at this at the moment. > > So how can I ensure to only get one of each? Distinct does not work. > > Travis -- Diana Soares -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
