simple LEFT JOIN giving wrong results ... - Mailing list pgsql-sql

From Marc G. Fournier
Subject simple LEFT JOIN giving wrong results ...
Date
Msg-id 20031211165902.I17041@ganymede.hub.org
Whole thread Raw
Responses Re: simple LEFT JOIN giving wrong results ...
Re: simple LEFT JOIN giving wrong results ...
List pgsql-sql
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.trafficct  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 |
247945531089 | 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
byip_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


pgsql-sql by date:

Previous
From: Michael A Nachbaur
Date:
Subject: Using chkpass() in a query
Next
From: Tom Lane
Date:
Subject: Re: simple LEFT JOIN giving wrong results ...