July 21, 2003 at 7:34 pm
I am missing something simple, but cannot quite see it for some reason. Each outer join doubles the results. can you please give me so advise in the correct direction to go. thanks in advance
SELECT distinct a.ccr_name, avg(a.volume) as volume,
sum(b.ptp) as ptp, sum(c.allocated) as allocated,
sum(c.available) as available, sum(c.talk) as talk, sum (c.wrap) as wrap,
sum(d.dialattempts) as dialattempts,sum (e.workvolume) as strategyvolume,
sum(f.contacts) as contacts, sum (g.accts_called) as accts_called,sum(h.dialerconnects) as dialerconnects,
sum(i.abandons) as abandons
from tbl_calltable a ,
left outer join tbl_ptp b on a.ccr_name = b.ccr_name
left outer join tbl_time c on a.ccr_name = c.ccr_name
left outer join tbl_dialattempts d on a.ccr_name = d.ccr_name
left outer join tbl_workvolume e on a.ccr_name = e.ccr_name
left outer join tbl_contacts f on a.ccr_name = f.ccr_name
left outer join tbl_acctscalled g on a.ccr_name = g.ccr_name
left outer join tbl_dialerconnects h on a.ccr_name = h.ccr_name
inner join tbl_abandons i on a.ccr_name = i.ccr_name
group by a.ccr_name
July 22, 2003 at 12:58 am
I don't know exactly what you are trying to do, but it seems to me this is normal behaviour, due to your data.
If we are talking about one ccr_name in tbl_calltable (with only one row), suppose you have 2 rows in tbl_ptp for this ccr_name and 2 rows in tbl_time for the ccr_name.
In that case a join on these three tables will give you a total of 1*2*2 = 4 records. So the data from tbl_ptp and tbl_time will be doubled for each combination.
Try running a select * for one a.ccr_name instead of all the aggregates, and you will see what is happening.
To solve this, try to do the aggregates inside subqueries. For two tables this would look like :
SELECT a.ccr_name, b.total_ptp
FROM (SELECT ccr_name, avg(volume) AS volume
FROM tbl_calltable
GROUP BY ccr_name) a
LEFT OUTER JOIN
(SELECT ccr_name, sum(ptp) AS total_ptp
FROM tbl_ptp GROUP BY ccr_name) b
ON a.ccr_name = b.ccr_name
July 22, 2003 at 9:40 am
Thank you this works great for me.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply