Home » SQL & PL/SQL » SQL & PL/SQL » Alternative to SubQuery (Oracle9i Enterprise Edition Release 9.2.0.8.0)
Alternative to SubQuery [message #445061] Thu, 25 February 2010 09:04 Go to next message
pkirangi
Messages: 74
Registered: August 2005
Member
I have a table which has the following structure

Order_id    Journal_type charge 
1000        Expense      1.23
1000        Expense      1.31
1000        Expense      43.21
1000        Revenue      12.23
1000        Revenue      1.03


This is the outcome I require

Result--A
Order_id    Journal_type charge Total_expense Total_revenue
1000        Expense      1.23   45.75         13.26
1000        Expense      1.31   45.75         13.26
1000        Expense      43.21  45.75         13.26
1000        Revenue      12.23  45.75         13.26
1000        Revenue      1.03   45.75         13.26


The easiest way is to use two subquery in the main select clause, to get the data.
But I wanted to avoid subqueries for performance reasons and so tried using Analytical functions.

select order_id, 
       Journal_type, 
       charge,
       sum(charge) over (partition by order_id,journal_type) Total_Expense,
       sum(charge) over (partition by order_id,journal_type) Total_Revenue
from
charges


But unfortunately the result which I get is as follows.

Result --B
Order_id    Journal_type charge Total_expense Total_revenue
1000        Expense      1.23   45.75         45.75
1000        Expense      1.31   45.75         45.75
1000        Expense      43.21  45.75         45.75
1000        Revenue      12.23  13.26         13.26
1000        Revenue      1.03   13.26         13.26


Can anyone please help me to get Result --A, using the analytical function query mentioned above?

Thanks
PHK

[Updated on: Thu, 25 February 2010 09:08]

Report message to a moderator

Re: Alternative to SubQuery [message #445062 is a reply to message #445061] Thu, 25 February 2010 09:22 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Try this:
select order_id, 
       Journal_type, 
       charge,
       sum(charge) over (partition by order_id) Total_Expense,
       sum(charge) over (partition by order_id) Total_Revenue
from
charges
Re: Alternative to SubQuery [message #445064 is a reply to message #445062] Thu, 25 February 2010 09:34 Go to previous messageGo to next message
pkirangi
Messages: 74
Registered: August 2005
Member
Thanks JRowbottom for your quick reply.
Unfortunately this yeilds

Order_id    Journal_type charge Total_expense Total_revenue
1000        Expense      1.23   59.01         59.01
1000        Expense      1.31   59.01         59.01
1000        Expense      43.21  59.01         59.01
1000        Revenue      12.23  59.01         59.01
1000        Revenue      1.03   59.01         59.01


Not exactly what I was looking for.

Thanks
PHK

Re: Alternative to SubQuery [message #445065 is a reply to message #445064] Thu, 25 February 2010 09:35 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Thanks JRowbottom for your quick reply.
Unfortunately this yeilds

And if you'd posted a working Test case: create table and insert statements along with the result you want with these data, we will be able to work with them and provide you some tested solutions.
Also if you give the formula to obtain the result or explain in words what you want, it would help.

Regards
Michel

[Updated on: Thu, 25 February 2010 09:37]

Report message to a moderator

Re: Alternative to SubQuery [message #445066 is a reply to message #445061] Thu, 25 February 2010 09:36 Go to previous messageGo to next message
cookiemonster
Messages: 12409
Registered: September 2008
Location: Rainy Manchester
Senior Member
select order_id, 
       Journal_type, 
       charge,
       sum(CASE WHEN Journal_type = 'Expense' THEN charge ELSE 0 END) over (partition by order_id) Total_Expense,
       sum(CASE WHEN Journal_type = 'Revenue' THEN charge ELSE 0 END) over (partition by order_id) Total_Revenue
from
charges;
Re: Alternative to SubQuery [message #445069 is a reply to message #445061] Thu, 25 February 2010 09:42 Go to previous messageGo to next message
pkirangi
Messages: 74
Registered: August 2005
Member
Thats absolutely fair Michel.

So here you go

create table charges(Order_id    Number,
                     Journal_type varchar2(20), 
                     charge number);

insert into charges values(1000 ,'Expense',1.23);
insert into charges values(1000 ,'Expense',1.31);
insert into charges values(1000 ,'Expense',43.21);
insert into charges values(1000 ,'Revenue',12.23);
insert into charges values(1000 ,'Revenue',1.03);



Thanks
PHK
Re: Alternative to SubQuery [message #445071 is a reply to message #445066] Thu, 25 February 2010 09:46 Go to previous messageGo to next message
pkirangi
Messages: 74
Registered: August 2005
Member
cookiemonster wrote on Thu, 25 February 2010 09:36
select order_id, 
       Journal_type, 
       charge,
       sum(CASE WHEN Journal_type = 'Expense' THEN charge ELSE 0 END) over (partition by order_id) Total_Expense,
       sum(CASE WHEN Journal_type = 'Revenue' THEN charge ELSE 0 END) over (partition by order_id) Total_Revenue
from
charges;


Awesome CookieMonster, just what I was looking for. You are the best.

Regards,
PHK

[Updated on: Thu, 25 February 2010 09:46]

Report message to a moderator

Re: Alternative to SubQuery [message #445072 is a reply to message #445069] Thu, 25 February 2010 09:48 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> select order_id, journal_type, charge,
  2         sum(decode(journal_type,'Expense',charge)) over (partition by order_id) total_expense,
  3         sum(decode(journal_type,'Revenue',charge)) over (partition by order_id) total_revenue
  4  from charges;
  ORDER_ID JOURNAL_TYPE             CHARGE TOTAL_EXPENSE TOTAL_REVENUE
---------- -------------------- ---------- ------------- -------------
      1000 Expense                    1.23         45.75         13.26
      1000 Expense                    1.31         45.75         13.26
      1000 Revenue                    1.03         45.75         13.26
      1000 Revenue                   12.23         45.75         13.26
      1000 Expense                   43.21         45.75         13.26

5 rows selected.

Regards
Michel

[Edit: Doh! too late]

[Updated on: Thu, 25 February 2010 09:48]

Report message to a moderator

Re: Alternative to SubQuery [message #445073 is a reply to message #445072] Thu, 25 February 2010 10:01 Go to previous message
pkirangi
Messages: 74
Registered: August 2005
Member
Michel Cadot wrote on Thu, 25 February 2010 09:48
SQL> select order_id, journal_type, charge,
  2         sum(decode(journal_type,'Expense',charge)) over (partition by order_id) total_expense,
  3         sum(decode(journal_type,'Revenue',charge)) over (partition by order_id) total_revenue
  4  from charges;
 

Regards
Michel

[Edit: Doh! too late]


Thanks Michel nonetheless. You were wonderful to help me out.
I noticed you post solutions to so many queries. It take a great heart to be giving day in day out tirelessly, solutions to unique problems posed by so many users.

Have a great day.
Regards,
PHK
Previous Topic: oracle enterprise user
Next Topic: Oracle bug with TimeStamp with Local Time Zone
Goto Forum:
  


Current Time: Tue Dec 06 14:37:18 CST 2016

Total time taken to generate the page: 0.09113 seconds