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.
2 messages in com.mysql.lists.mysqlRe: tough sql joining and aggregate q...
