Revokez Revokez - 2 years ago 83
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:


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


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



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 Source

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;
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download