From: Marc G. Fournier <scrappy@hub.org>
To: pgsql-sql@postgresql.org
Subject: simple LEFT JOIN giving wrong results ...
Date: Thu, 11 Dec 2003 17:01:22 -0400 (AST)
Message-ID: <20031211165902.I17041@ganymede.hub.org> (raw)
I've got to be missing something obvious here ... I have two tables, on
containing traffic stats, the other disk usage ... I want to do a JOIN in
one query to give me the sum of traffic and average of storage ...
seperately, the results are right .. put together, traffic values are way
off, while storage is still correct ...
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;
ip_id | traffic | storage
-------+--------------+-------------
1194 | 76761728 | 1839676259
1226 | 5744576925 |
1134 | 17042528 | 24794553
1089 | 311779796360 | 10814211187
1200 | 82535202840 | 3165073628
1088 | 1969333472 | 2119206061
1227 | 44816947957 | 4891683299
1179 | 3867502285 |
(8 rows)
where, individually, the results should be:
ams=# select ip_id, avg(bytes)::bigint from company_00186.storage
where month_trunc(runtime) = '2003-12-01' group by ip_id;
ip_id | avg
-------+-------------
1227 | 4891683299
1255 | 0
1134 | 24794553
1194 | 1839676259
1089 | 10814211187
1088 | 2119206061
1200 | 3165073628
(7 rows)
and
ams=# select ip_id, sum(bytes) from company_00186.traffic
where month_trunc(runtime) = '2003-12-01' group by ip_id;
ip_id | sum
-------+-------------
1194 | 9595216
1226 | 5744576925
1134 | 2130316
1089 | 38972474545
1200 | 10316900355
1088 | 246166684
1227 | 44816947957
1179 | 3867502285
(8 rows)
the storage/avg values come out right in the JOIN, but the traffic/sum
values get royally screwed ...
----
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@hub.org
Subject: Re: simple LEFT JOIN giving wrong results ...
In-Reply-To: <20031211165902.I17041@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