How to join the table like this? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

MS Access

How to join the table like this?

Author

Topic

man889
Starting Member

25 Posts

Posted - 2011-06-14 : 03:53:29

I have two table as follow

TableA
PID, Amount1
1, 10
1, 20
2, 10
2, 10
3, 30

TableB
PID, Amount2
1, 20
1, 50
2, 30
2, 20

SELECT TableA.PID, Sum(TableA.Amount1) AS Amount1OfSum, Sum(TableB.Amount2) AS Amount2OfSum
FROM TableA left JOIN TableB ON TableA.PID = TableB.PID
GROUP BY TableA.PID;

The output is
PID, Amount1, Amount2
1, 90, 200
2, 20, 40
3, 30,

What I want the output is
PID, Amount1, Amount2
1, 30, 70
2, 20, 50
3, 30,

Thank.

raghuveer125
Constraint Violating Yak Guru

285 Posts

Posted - 2011-06-14 : 05:10:54

Try this

Select t.Pid,t.Amount1,tt.Amount2 from (SElect pid,Sum(Amount1) as Amount1 from tableA Group by Pid) as t left join
(Select pid,Sum(Amount2) as Amount2 from tableB Group by Pid)as tt on t.pid=tt.pid

In Love... With Me!

man889
Starting Member

25 Posts

Posted - 2011-06-14 : 05:21:00

Hi raghuveer125,

Thank for your's reply

I will try it tmr

man889
Starting Member

25 Posts

Posted - 2011-06-14 : 21:55:18

Hi raghuveer125,

I got it, thank.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources