Re: simple LEFT JOIN giving wrong results ...

On Thu, 11 Dec 2003, Tom Lane wrote:

> "Marc G. Fournier" <scrappy(at)hub(dot)org> writes:
> > So, I'm doing my LEFT JOIN wrong *somehow*, but its eluding me as to
> > what/how .. :(
>
> > ams=# select ct.ip_id, sum(ct.bytes) as traffic,
> > avg(cs.bytes)::bigint as storage
> > from company_00186.traffic ct
> > left join company_00186.storage cs ON ( ct.ip_id = cs.ip_id AND
> > month_trunc(cs.runtime) = '2003-12-01')
> > where month_trunc(ct.runtime) = '2003-12-01' group by ct.ip_id;
>
> I suspect you do not want the month_trunc constraint to be included
> in the JOIN ON condition, only in WHERE.

'k, but then would that take in all storage for all dates, since I'm only
then joining on the ip_id? right now, I only have storage #s for Dec, so
it wouldn't make any differences for this one, but ..

results are still way off though, even with removing it:

ip_id | traffic | storage
-------+--------------+-------------
1088 | 1979325872 | 2119206061
1200 | 84004842024 | 3165073628
1227 | 45591571353 | 4891683299
1179 | 3893192839 |
1194 | 77360968 | 1839676259
1134 | 17357504 | 24794553
1226 | 5836213601 |
1089 | 315424415080 | 10814211187
(8 rows)

By changing the query to:

ams=# select ip_id, sum(bytes),
(select avg(bytes)
from company_00186.storage cs
where month_trunc(runtime) = '2003-12-01'
and cs.ip_id = ct.ip_id)::bigint as storage
from company_00186.traffic ct
where month_trunc(runtime) = '2003-12-01' group by ip_id;
ip_id | sum | storage
-------+-------------+-------------
1194 | 9670121 | 1839676259
1134 | 2169688 | 24794553
1226 | 5836213601 |
1089 | 39428051885 | 10814211187
1088 | 247415734 | 2119206061
1200 | 10500605253 | 3165073628
1227 | 45591571353 | 4891683299
1179 | 3893192839 |
(8 rows)

I can get the right results again, it jus doesn't seem as clean ;(

----
Marc G. Fournier Hub.Org Networking Services (http://www.hub.org)
Email: scrappy(at)hub(dot)org Yahoo!: yscrappy ICQ: 7615664