Allocated Cost Calculation in Oracle PL SQL Query [logical]

Question

I am stuck with a logical problem in my application. I have been trying to find the allocated cost based on their Customers ID using SQL. My problem goes like this: In first table(Table1) I have BusinessUnit, ProductFamily,Cost

in the other(Table2) I have BusinessUnit, CustomerID, ProductFamily , Revenue

Data as below `

 BU      Product_Family   Cost
 ------------------------------ 
  4      Fiat             145 
  5      TI               200

`

BU  CID Product_Family  Revenue
-----------------------------------
 4  c1    Fiat            50
 4  c2    Fiat            80
 4  c3    Fiat            40
 5   c3         TI               40
 5   c2       TI               80
 Sum_of_Revenue for BU,Product_Family wise [4,Fiat]:               (50+80+40) = 170

Now I need to find the allocated cost per CID(Customer_ID) : Calculation goes like this

Allocated Cost for C1 for BU,Product_Family wise [4,Fiat] = [Table1.Cost(Here the value is 145) * Table2.Cost of C1 Customer(here the value is 50)] / Sum_of_Revenue BU,Product_Family wise [4,Fiat] (here the value is 170).

And for C3 [BU,Product_Family wise (5,TI)]the value should be (200*40)/(40+80)

Would you please suggest me how would I desing my code to accomplish this task.

Answer 1

try following select

select 
BusinessUnit, CustomerID, ProductFamily , Revenue
, (table1.Cost * table2.Revenue ) 
  / (SUM(Revenue) over(partition by table1.BusinessUnit, table1.ProductFamily )) 
                 AS  Allocated_Cost
from 
    table1 
    INNER JOIN table2 ON ( table1.BusinessUnit =  table2.BusinessUnit
                          AND table1.ProductFamily = table2.ProductFamily )
;