Inner join as sub Query

[Kunal Lakhani]

create table A (
id number(6)
, Amount number(10)
);

create table B (
id number(6)
, Amount number(10)
);

insert into A values (1, 50);
insert into A values (2, 500);
insert into A values (1, 600);
insert into A values (2, 900);
insert into A values (1, 600);

insert into B values (2, 30);
insert into B values (1, 300);
insert into B values (1, 100);
insert into B values (1, 900);
insert into B values (1, 770);

This is what i want
id = 1
sum(A.amount) = 1250
sum(B.amount) = 2070
(offcourse, a single row)

This gives wrong data :
select sum(A.amount),sum(B.amount) from A,B where A.id = B.id and A.id = 1

select A.id,sum(A.amount),sum(B.amount) from A,B where A.id = B.id(+) and A.id = 1 group by A.id

The wrong output is

id = 1
sum(A.amount) = 5000
sum(B.amount) = 6210
(offcourse, a single row)

The reason for wrong output is, that the no. of rows with id 1 in table A differs from the no. of rows with id 1 in table B

I tried

SELECT * FROM (
SELECT ID, SUM(amount) FROM A WHERE id=1
INNER JOIN
SELECT ID, SUM(amount) FROM B where id=1
ON A.id = B.id )

But it gives "ORA - 00907 - missing right parenthesis" error

What should i do?

[urtrivedi]

you dont need join, join is applicable if you have fk-pk relation
you try following query

select id, sum(suma), sum(sumb) from
(select id, amount as suma, 0 as sumb from a where id=1
union
select id, 0 suma , amount as sumb from b where id=1
) x group by id

[Kunal Lakhani]

Thanks urtrivedi. i have came up with a solution. Its

select A.id, A.summed, B.summed  
from   
(select id, sum(amount) summed from A where id = 1 group by id) A  
left join  
(select id, sum(amount) as summed from B where id = 1 group by id) B  
on A.id = B.id  

[urtrivedi]

your query will fail if A have no data for id=1, it will also not show summedB even if table B having data for id 1