join help

  • 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

  • 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
  • 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