Home » SQL & PL/SQL » SQL & PL/SQL » query to right outer join and sum up (Oracle 11.2.0.3)
query to right outer join and sum up [message #650194] Tue, 19 April 2016 05:58 Go to next message
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 #650195 is a reply to message #650194] Tue, 19 April 2016 06:14 Go to previous messageGo to next message
John Watson
Messages: 8931
Registered: January 2010
Location: Global Village
Senior Member
One solution,

select id,sum(nvl(app_1.sal,0)+nvl(app_2.sal,0)) from app_1 full outer join app_2 using(id) group by id;
Re: query to right outer join and sum up [message #650196 is a reply to message #650194] Tue, 19 April 2016 06:16 Go to previous messageGo to next message
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

You don't need any join.
Use UNION ALL in an inline view and sum up in the outer query/
SELECT id, sum()
FROM (SELECT ... UNION ALL SELECT ...)
GROUP BY id


Re: query to right outer join and sum up [message #650198 is a reply to message #650196] Tue, 19 April 2016 06:37 Go to previous messageGo to next message
pointers
Messages: 451
Registered: May 2008
Senior Member
Thank you very much John and Micheal.

Regards,
Pointers
Re: query to right outer join and sum up [message #650199 is a reply to message #650198] Tue, 19 April 2016 06:40 Go to previous messageGo to next message
John Watson
Messages: 8931
Registered: January 2010
Location: Global Village
Senior Member
Which solution do you prefer? Which do you think would perform better? Under what circumstances?

It is only fair to give a bit more in reply.
Re: query to right outer join and sum up [message #650201 is a reply to message #650199] Tue, 19 April 2016 07:00 Go to previous message
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
Previous Topic: Bulk collect data problem
Next Topic: TO_DATE formating
Goto Forum:
  


Current Time: Wed Apr 24 10:54:29 CDT 2024