On Thu, 11 Dec 2003, Tom Lane wrote:
> "Marc G. Fournier" <scrappy@hub.org> 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
wheremonth_trunc(runtime) = '2003-12-01' and cs.ip_id = ct.ip_id)::bigint as storage from
company_00186.trafficct 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: scrappy@hub.org Yahoo!: yscrappy ICQ: 7615664