CODEWITHSUNDEEP
sqlsql-server
Revokez
Revokez

Reputation: 323

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

Upvotes: 1

Views: 50

Answers (2)

Deepthi
Deepthi

Reputation: 86

CREATE TABLE #Table1(ID  INT,
                    Value1  INT,
                    JoiningValue INT)
INSERT INTO #Table1 VALUES
(1,3,1)
,(2,2,2)
,(3,3,2)

CREATE TABLE #Table2(ID INT,
                    value1 INT,
                    JoiningValue INT)

INSERT INTO #Table2 VALUES
(1,5,3)
,(2,2,3)
,(3,2,3)
,(4,1,2)


CREATE TABLE #Join(ID INT)
INSERT INTO #Join VALUES
(1)
,(2)
,(3)
,(4)
,(5)



SELECT  SUM(T1.Value1) ValueSum1,
        SUM(T2.value1) ValueSum2
FROM 
#Join J
INNER JOIN #Table1 T1
    ON J.ID = T1.ID
RIGHT JOIN #Table2 T2
    ON J.ID = T2.ID

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269773

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;

Upvotes: 3

Related Questions