query to right outer join and sum up [message #650194] |
Tue, 19 April 2016 05:58 |
pointers
Messages: 451 Registered: May 2008
|
Senior Member |
|
|
Hi,
create table app_1(id number, sal number)
/
create table app_2 (id number, sal number)
/
begin
insert into app_1 values (1, 10);
insert into app_1 values (2, 10);
insert into app_1 values (3, 10);
insert into app_1 values (4, 10);
--
insert into app_2 values (3, 10);
insert into app_2 values (4, 10);
insert into app_2 values (5, 10);
insert into app_2 values (6, 10);
end;
/
Required Output: I would like to get all the Ids from both the tables where ever IDs are matching the corresponding sal should be summed up, non joining IDs should be displayed with the sal values.
O/P:
ID SUM_VAL
1 10
2 10
3 20
4 20
5 10
6 10
I wrote the below code, but Can you please show me any other ways of doing it.
SELECT app_1.ID, app_1.sal + NVL (app_2.sal, 0) sum_val
FROM app_1
LEFT OUTER JOIN app_2
ON app_1.ID = app_2.ID
UNION
SELECT app_2.ID, NVL (app_1.sal, 0) + app_2.sal
FROM app_1
RIGHT OUTER JOIN app_2
ON app_1.ID = app_2.ID
Thank you in advance.
Regards,
Pointers
|
|
|
|
|
|
|
Re: query to right outer join and sum up [message #650201 is a reply to message #650199] |
Tue, 19 April 2016 07:00 |
pointers
Messages: 451 Registered: May 2008
|
Senior Member |
|
|
The query was only for discussion purpose, actual query got some other joins etc. But the idea is same.
In overall,
- Worst was my query it took more than 1 minute.
- Micheal approach took around 36 seconds 89 seconds
- John approach took around 36 seconds 46 seconds
I've picked up John approach as the final and modified accordingly, the reason being it is concise.
Nevertheless, the intention was me knowing different ways of doing it, thus my thanks to both of you.
Regards,
Pointers
|
|
|