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