Outer join and sums

[ks1392]

Hi, I'm trying to join three (actually more) tables together with the purpose of getting user id and sums of various integers. Here's what I'm doing with a two table join, which works fine.

SELECT pcs.csr_sbcuid, SUM(b.tkts_scrnd)
FROM personnel_csb_snapshot pcs, bo_tkts_scrnd b
WHERE pcs.csr_lmos *= b.lmos AND pcs.themonth *= b.themonth AND pcs.theyear *= b.theyear
AND csr_job_function like 'rts' AND pcs.themonth = '12' AND pcs.theyear = '2003'
GROUP BY pcs.csr_sbcuid;

I do an outer join on the pcs table because I would ultimately like to incorporate other statistics tables (those beginning with bo_) and those tables may or may not have an entry for a given user id. As soon as I add a third table, like in the example below, the sums go through the roof. I thought it had to do with grouping, but I've tried all kinds of combinations to no avail. Does anyone know if I can accomplish this with one query? Right now I'm doing about six queries of two table joins -- blegh! TIA!

SELECT DISTINCT pcs.csr_sbcuid, SUM(bo1.tkts_scrnd), SUM(bo2.qual_disp),
SUM(bo2.non_qual_disp), SUM(bo2.total_disp)
FROM personnel_csb_snapshot pcs, bo_tkts_scrnd bo1, bo_non_qual_disp bo2
WHERE pcs.csr_lmos *= bo1.lmos AND pcs.themonth *= bo1.themonth AND pcs.theyear *= bo1.theyear
AND pcs.csr_lmos *= bo2.lmos AND pcs.themonth *= bo2.themonth AND pcs.theyear *= bo2.theyear
AND csr_job_function like 'rts' AND pcs.themonth = '12' AND pcs.theyear = '2003'
GROUP BY pcs.csr_sbcuid;

 

[swampBoogie]

What happens is a multplication effect.

The sum will get multiplied by the number of matching rows in the bo_non_qual_disp table.

You better keep it as two separate queries. Also, you don't need a distinct qualifier. Also, you shouldn't use the (soon) obsolete syntax for outer join but rather

SELECT pcs.csr_sbcuid, SUM(b.tkts_scrnd)
FROM personnel_csb_snapshot pcs left join bo_tkts_scrnd b
on pcs.csr_lmos = b.lmos 
AND pcs.themonth = b.themonth 
AND pcs.theyear = b.theyear
where csr_job_function = 'rts' 
AND pcs.themonth = '12' 
AND pcs.theyear = '2003'
GROUP BY pcs.csr_sbcuid

 

[ks1392]

Shucks, that's what I figured. I wasn't aware of the *= syntax being deprecated - thanks for the tip.

 

[SemperFiDownUnda]

For those that are wondering what happened here its like this


The left table might look something like this

ID   SomeValue
1    13
2    4
2    8
3    3
3    8
3    2
3    7
4    2

the right table might look like this

ID   SomeOtherValue
1    2
1    3
3    8 
3    9
3    3

if you join these tables like this

SELECT A.ID, SUM(A.SomeValue), SUM(B.SomeOtherValue)
  FROM TableA A
  LEFT OUTER JOIN TableB B
    ON A.ID = B.ID
 GROUP BY A.ID

You have to remember that the Aggragate functions gets executed after the initial result set is created. For the tables above this would look like this

ID SomeValue

 SomeOtherValue

1  13

 2

1  13

 3

2  4

 NULL

2  8

 NULL

3  3

 8

3  3

 9

3  3

 3

3  8

 8

3  8

 9

3  8

 3

3  2

 8

3  2

 9

3  2

 3

3  7

 8

3  7

 9

3  7

 3

4  2

 NULL

Then the 2nd pass is done doing the aggregate functions like SUM() and you get

ID SUM(SomeValue)

 SUM(SomeOtherValue)

1  26

 5

2  12

 0

3  60

 80

4  2

 0

Hope I've been helpful,
Wayne Francis

If you want to get the best response to a question, please check out FAQ222-2244 first