0

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?

2
Contributors
3
Replies
5
Views
4 Years
Discussion Span
Last Post by urtrivedi
0

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
0

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  
0

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

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.