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.

you are joining and then grouping

you should be grouping and then joining --

hi r937,

Thank for your's reply.

I will try it tmr.

What's a tmr?

the day after tdy

Hi r937,

I got it, thank.

the first start make a database and then two file combine with each other.
and then join the table

Sorry I am new to SQL
I executed the following query :


SELECT one.pid
, one.Amount1OfSum
, two.Amount2OfSum
FROM ( SELECT pid
, SUM(Amount1) AS Amount1OfSum
FROM TableA
GROUP
BY pid ) AS one
LEFT OUTER
JOIN ( SELECT pid
, SUM(Amount2) AS Amount2OfSum
FROM TableB
GROUP
BY pid ) AS two
ON two.pid = one.pid


and got the error ORA: 00933 sql command not properly ended....Cud you plz tell me where I went wrong ?

jason, dude, this isn't your thread, perhaps you should re-post in the oracle forum

Okay. I will repost the same query in the oracle forum