Inner join as a sub Query

[Kunal Lakhani]

I am providing all the details (CREATE/INSERT queries ) so that one can run it.
I want to sum a column as per id of two different tables and join it and produce a single row

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

How should i proceed?

[Martin Vashko]

A for providing the scripts!

Your observation regarding your first query

Kunal Lakhani wrote:
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

is not entirely correct. That query would give the output you expect if and only if both tables contained exactly one row with ID=1. If both tables contained, say, two rows with ID=1, the output would be wrong different from what you want. I'll leave it on you to figure out why is this so.

You're heading in the right direction with the last query, but not yet exactly there. I'll provide the correct query and a short commentary:
SELECT * FROM (SELECT ID, SUM(amount) FROM A GROUP BY ID) A1 INNER JOIN (SELECT ID, SUM(amount) FROM B GROUP BY ID) B1 ON (A1.id = B1.id) where A1.id=1; Basically, you're trying to join two subqueries. A subquery has to be enclosed in parenthesis (lines 2 and 4) and can be used wherever a table can be used. You can (or, in this case, need to) specify names for the subqueries, I've used A1 and B1.

The query joins the two subqueries on the ID column; if you omit the where clause, you'll get results for all IDs that appear in both tables.

Apart from not parenthesizing the inner queries, you've forgotten to provide a GROUP BY. We could do without the group by if you really ever need to select just one ID from both tables:
SELECT * FROM (SELECT SUM(amount) FROM A WHERE ID=1), (SELECT SUM(amount) FROM B WHERE ID=1); This is actually special case of a join, expressed in the old (Oracle's) syntax. Since we didn't specify any join condition, the result is a cartesian product of all the rows from both tables (or subqueries, in our case). Since both tables contain just one row, the result has one row too.

[Kunal Lakhani]

Thanks a lot Martin Vajsar

I came with a solution :

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

That's pretty similar to what you did. Just Instead of "*", i named the columns