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 = and = 1

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

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 ID, SUM(amount) FROM B where id=1
ON = )

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

What should i do?

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
select id, 0 suma , amount as sumb from b where id=1
) x group by id

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

select, A.summed, B.summed  
(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 =  

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

Be a part of the DaniWeb community

We're a friendly, industry-focused community of 1.18 million developers, IT pros, digital marketers, and technology enthusiasts learning and sharing knowledge.