CODEWITHSUNDEEP
mysqlsqlgroup-bysum
Shadol
Shadol

Reputation: 100

Joining the same table 2 times, then summing up -> doubles the actual wanted value

I have a SQL query that looks like this:

select L.ID, P.FIRSTNAME as Firstname, P.LASTNAME as Lastname, SUM(R.VAL), SUM(R2.VAL) from LABORATORY L
join RESULT R
    on L.ID=R.ID 
    and R.RESID='Thingy1'
join PERSON P
    on P.PERID=R.PERID
    and P.LASTNAME={Lastname} <-this is part of the framework I'm working with and gets replaced, don't worry about it
    and P.FIRSTNAME={Firstname}
join RESULT R2
    on R2.PERID=P.PERID
    and R2.RESID='Thingy2'

Now since I'm joining the Result Table 2 times, both sums are double the value they should be. If I leave the SUMs I get this table as a result.

123 Manu Dreier

234 Michael Schuhmacher

423 Barrack Obama

123 Manu Dreier

234 Michael Schuhmacher

423 Barrack Obama

Sorry I don't know how to correctly display a table here :P

As you can see, every value in this table appears 2 times. If I add "distinct" there is only 3 values( correct ) but the value of SUM is still doubled. Any clue about what I could do about this would be helpful. With count I can actually write:

count(distinct L.ID)

But this doesn't work with the sums since some people have identical values (like age for example). Summing up distinct ages to get the total peoples age doesn't work ;)

Upvotes: 1

Views: 42

Answers (2)

GMB
GMB

Reputation: 222582

You seem to be dealing with a N-M join. To avoid double-computing SUMs, one solution is to move the aggregate query to the JOINs, like so :

select 
    L.ID, 
    P.FIRSTNAME as Firstname, 
    P.LASTNAME as Lastname, 
    R1.sum_val, 
    R2.sum_val
from LABORATORY L
join (SELECT ID, SUM(VAL) sum_val FROM RESULT R WHERE RESID='Thingy1' GROUP BY ID) R
    on L.ID=R.ID 
join PERSON P
    on P.PERID=R.PERID
    and P.LASTNAME={Lastname}
    and P.FIRSTNAME={Firstname}
join (SELECT PERID, SUM(val) FROM RESULT WHERE R2.RESID='Thingy2' GROUP BY PERID) R2
    on R2.PERID=P.PERID

With this technique, aggregation happens within the joined subqueries, and does not affect the outer query.

Upvotes: 1

ScaisEdge
ScaisEdge

Reputation: 133380

You should join the subquery for sum 

  select L.ID, P.FIRSTNAME as Firstname, P.LASTNAME as Lastname, r1.sum_1, r2.sum_2
  from LABORATORY L 
  INNER JOIN (
    select  ID, PERID,  SUM(VAL) sum_1
    from RESULT 
    where RESID='Thingy1'
    group by ID, PERID
  ) r1 on r1.PERID = P.PERID AND r1.ID = L.ID
  INNER JOIN (
    select  ID, PERID,  SUM(VAL) sum_2
    from RESULT 
    where RESID='Thingy2'
    group by ID, PERID

  ) r2 on r2.PERID = P.PERID AND r2.ID = L.ID
  INNER PERSON P ON P.PERID = r1.PERID

Upvotes: 1

Related Questions