Home » SQL & PL/SQL » SQL & PL/SQL » Summing of record values based on certain condition (Pracle 10g 10.0.2.0)
Summing of record values based on certain condition [message #347053] Wed, 10 September 2008 09:26 Go to next message
kumarprabhash@gmail.com
Messages: 2
Registered: September 2008
Junior Member
<pre>


PART_NO COMMODITY PLATFORM SUPPLIER_T1 SUPPLIER_T2 REBATE
---------------------------------------------------------------------------------------
PART1 COMM1 PFORM1 SUPP1 SUPP3 10
PART1 COMM2 PFORM1 SUPP1 SUPP3 10
PART1 COMM3 PFORM1 SUPP1 SUPP3 10
PART1 COMM5 PFORM1 SUPP1 SUPP3 10
PART1 COMM6 PFORM1 SUPP1 SUPP3 10
PART1 COMM7 PFORM1 SUPP1 SUPP3 10
PART2 COMM4 PFORM1 SUPP2 SUPP3 10
PART2 COMM5 PFORM1 SUPP2 SUPP3 10
PART3 COMM5 PFORM1 SUPP2 SUPP3 10
---------------------------------------------------------------------------------------

If this condition is satisfied then we need to do the following:
Condition:
If for same part_no- platform, supplier_t1 is same and commodity is different - then add the rebate amount for that record and make a single
record with same part_no, platform, supplier_t1 and any of the commodity for that same part no (here it sould be COMM4 OR COMM5 for PART2

Finally we need to have the following records in another table

PART_NO COMMODITY PLATFORM SUPPLIER_T1 SUPPLIER_T2 REBATE
---------------------------------------------------------------------------------------
PART1 COMM1 PFORM1 SUPP1 SUPP3 50
PART2 COMM4 PFORM1 SUPP2 SUPP3 20
PART3 COMM5 PFORM1 SUPP2 SUPP3 10

Any help as how to do it programatically is highly appreciable as I am in urgent need of it.

Thanks in advance

</pre>
Re: Summing of record values based on certain condition [message #347058 is a reply to message #347053] Wed, 10 September 2008 09:44 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & FOLLOW the Posting Guidelines as stated in URL above

Re: Summing of record values based on certain condition [message #347063 is a reply to message #347053] Wed, 10 September 2008 09:53 Go to previous messageGo to next message
developer69
Messages: 6
Registered: September 2008
Junior Member
I agree with anacedent.

About the query here are some hints:
You can use "sum(rebate)" to get the total value and "group by PART_NO, PLATFORM, SUPPLIER_T1" for grouping the results.
While selecting commodity like you said "any of the commodity for that same part no" so its up to you.

Thanks
Sam
Re: Summing of record values based on certain condition [message #347105 is a reply to message #347053] Wed, 10 September 2008 13:14 Go to previous message
coleing
Messages: 213
Registered: February 2008
Senior Member
also you can use thngs like:-

sum(case when condition1 = 'X' then 1 else 0 end)
Previous Topic: move segments from one tablespace to other tablespace
Next Topic: Creating Dynamic Variables
Goto Forum:
  


Current Time: Sat Dec 10 22:49:15 CST 2016

Total time taken to generate the page: 0.08027 seconds