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: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings
