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?