On Thu, 11 Dec 2003, Tom Lane wrote: > "Marc G. Fournier" <[EMAIL PROTECTED]> 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: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster