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 ')'.

I've run your statement on a 12.5.4 ASE without any errors.

hi aflorin27,

Thank very much for your reply.

I still get incorrect syntax error.
I think ASE 12.5 version don't support this syntax,
just like the left join have to use *= instead of "left join"

I can't find the solution, do you know any other way to do the same query?

thank.

select a.pid, sum(a.Amount1), sum(a.Amount2)
from
(
select pid, Amount1, 0 as Amount2
from TableA
union all
select pid, 0 as Amount1, Amount2
from TableB
) a
group by a.pid;

Hi aflorin27,

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