I am using Sybase ASE 12.5
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 one.pid
, one.Amount1OfSum
, two.Amount2OfSum
FROM ( SELECT pid
, SUM(Amount1) AS Amount1OfSum
FROM TableA
GROUP
BY pid ) AS one,
( SELECT pid
, SUM(Amount2) AS Amount2OfSum
FROM TableB
GROUP
BY pid ) AS two
where two.pid *= one.pid

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

Incorrect syntax near the keyword 'select'.
Incorrect syntax near ')'.

Thank.

The "*=" is not SQL syntax. You want to have a LEFT OUTER JOIN or FULL OUTER JOIN with a join predicate of "one.pid = two.pid".

Hi stolze,

Thank very much for your reply.

I finally got the answer.
Sybase ASE 12.5.0.3 version is not support "derivedtable", so it is not the syntex problem.

I have to do it by using temp table or sp

Thank

Well, aside from the "*=" stuff, your syntax is fine and acceptable standard SQL. It seems that your product deviates from the standard, however.