From: Marc G. Fournier <scrappy@postgresql.org>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-sql@postgresql.org
Subject: Re: simple LEFT JOIN giving wrong results ...
Date: Thu, 11 Dec 2003 19:54:42 -0400 (AST)
Message-ID: <20031211194236.H17041@ganymede.hub.org> (raw)
In-Reply-To: <15860.1071180972@sss.pgh.pa.us>
References: <20031211165902.I17041@ganymede.hub.org>
<15860.1071180972@sss.pgh.pa.us>
On Thu, 11 Dec 2003, Tom Lane wrote:
> "Marc G. Fournier" <scrappy@hub.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@hub.org Yahoo!: yscrappy ICQ: 7615664
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: scrappy@postgresql.org, tgl@sss.pgh.pa.us
Subject: Re: simple LEFT JOIN giving wrong results ...
In-Reply-To: <20031211194236.H17041@ganymede.hub.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox