Revokez Revokez - 6 months ago 13
SQL Question

Getting two sums from different tables with a joining table

I'm trying to output the following:

ValueSum1 | ValueSum2
8 | 10


However I'm having a bit of difficulty. The Tables I have are:

Table1

ID Value1 JoiningValue
1 3 1
2 2 2
3 3 2


Table2

ID Value1 JoiningValue
1 5 3
2 2 3
3 2 3
4 1 2


JoinTable

ID
1
2
3
4
5


I need to be able to join the JoinTable to the query because I need to able to set a where condition specifically against values within the JoinTable.
I currently have:

SELECT SUM(Table1.Value1) 'ValueSum1',SUM(Table2.Value1) 'ValueSum2' From JoinTable
Left Join Table1
On JoinTable.ID = Table1.Joiningvalue
Left Join Table2
On JoinTable.ID = Table2.JoiningValue


However this is giving me the output of:

ValueSum1 ValueSum2
8 11

Answer

Aggregate the values before joining:

SELECT t1.ValueSum1, t2.ValueSum2
FROM JoinTable jt Left Join
     (SELECT Joiningvalue, SUM(Table1.Value1) as ValueSum1
      FROM Table1
      GROUP BY Joiningvalue
     ) t1
     On jt.ID = t1.Joiningvalue LEFT JOIN
     (SELECT Joiningvalue, SUM(Table2.Value1) as ValueSum2
      FROM Table2
      GROUP BY Joiningvalue
     ) t2
     On jt.ID = t2.JoiningValue;
Comments