title: Calculation using joins and select statements SQL
body: <p>I did ask for a part of this question <a href="https://stackoverflow.com/questions/21394286/subtract-value-to-multiple-rows-sql">here</a> just to take a push myself from the point where I was stuck. But, now I experience great problems to find a solution further. Well, I should say I have ended myself to a state where I could write one single complex query  which was purely logic. But, now I in this case I am aware of the logic but couldnt implement through writing. Here is the description of my problem:</p>
  
  <p>Problem : </p>
  
  <p>Table1:</p>
  
  <pre><code>REFID      ARTIKEL      SUPPLIERID      ORGID      PIECES      COSTPRICE      DISCOUNT
  126663      TV            SONY          922         6            110           2.5
  126663      TV            Toshiba       922         4            75            2
  126663      TV            SONY          922         10           80            1
  126663      TV            LG            922         10           80            1
  126663      TV            SONY          922         4           65            1.5
  126663      TV            SONY          922         14           95            1.5
  </code></pre>
  
  <p>Table2:</p>
  
  <pre><code>REFID      ARTIKEL      SUPPLIERID      ORGID     STOCK_SUM_PIECES     
  126663      TV            SONY          922         16            
  126663      TV            Toshiba       922         20
  </code></pre>
  
  <p>TABLE3:</p>
  
  <pre><code>REFID      ARTIKEL      SUPPLIERID      ORGID      STORE    SALE_SUM_PIECES     DATE   
  126663      TV            SONY          922        100          4            01.01.2014
  126663      TV            Toshiba       922        100          3            01.01.2014   
  </code></pre>
  
  <p>Operation to perform:</p>
  
  <p>Firstly I need to sum up the <code>STOCK_SUM_PIECES</code> and <code>SALES_SUM_PIECES</code> from <code>table2</code> and <code>table3</code> respectively.For example: SUM_RESULT is 16+4=20 for SONY from TABLE2 and TABLE3
  Next,  distribute the <code>SUM_RESULT</code> onto the rows in the <code>table1</code> as shown <a href="https://stackoverflow.com/questions/21394286/subtract-value-to-multiple-rows-sql">here</a>. In the process of distributing, once the value reaches zero, it does mean that only those rows are valid for that "SUM_PIECES" in TABLE2. For example: <code>SUM_RESULT 20</code> for SONY is valid only for three rows in <code>TABLE1</code> since number of pieces sum up to 20 i.e 6+10+4=20. Now select only these three rows and calculate the average of Cost price and Discount in TABLE1.</p>
  
  <p>This is the most complex part I am not able to solve. I am not asking for one to write a query instead to give me an idea or method to follow to end up with the solution. </p>
  
  <p>I am currently successful in making appropriate joins but cannot perform calculation during join. This is complex to me only because, I cannot use curosrs or for loop and so on since I have to make it performance efficient and also data is huge maybe billion rows. So it has to be done using joins and select statements.
  Any suggestions Please help!</p>
  
  <p>Thanks</p>