title: Calculating the Weighted Average Cost of products stock
body: <p>I have to calculate my products stock cost, so for every product after each buy, i have to recalculate the <strong><a href="http://en.wikipedia.org/wiki/Average_cost_method" rel="nofollow">Weighted Average Cost</a></strong>.</p>
  
  <p>I got a view thats bring me the current product's stock after each in/out:</p>
  
  <pre><code>document_type   document_date   product_id  qty_out qty_in  price       row_num stock_balance
  SI              01/01/2014      52          0       600     1037.28     1           600
  SI              01/01/2014      53          0       300     1357.38     2           300
  LC              03/02/2014      53          100     0       1354.16     3           200
  LC              03/02/2014      53          150     0       1355.25     4           50
  LC              03/02/2014      52          100     0       1035.26     5           500
  LC              03/02/2014      52          200     0       1035.04     6           300
  LF              03/02/2014      53          0       1040    1356.44     7           1090
  LF              03/02/2014      52          0       1560    1045        8           1860
  LC              04/02/2014      52          120     0       1039.08     9           1740
  LC              04/02/2014      53          100     0       1358.95     10          990
  LF              04/02/2014      52          0       600     1038.71     11          2340
  LF              04/02/2014      53          0       1040    1363.3      12          2030
  LC              05/02/2014      52          100     0       1037.78     13          2240
  LF              15/03/2014      53          0       20      1365.87     14          2050
  LF              15/03/2014      52          0       50      1054.19     15          2290
  </code></pre>
  
  <p>I want to add a calculated <code>WAC</code> field as above:</p>
  
  <pre><code>document_type   document_date   product_id  qty_out qty_in  price           row_num     stock_balance   WAC 
  SI              01/01/2014      52          0       600     1 037,28        1           600             1037,28000000000
  SI              01/01/2014      53          0       300     1 357,38        2           300             1357,38000000000
  LC              03/02/2014      53          100     0       1 354,16        3           200             1357,38000000000
  LC              03/02/2014      53          150     0       1 355,25        4           50              1357,38000000000
  LC              03/02/2014      52          100     0       1 035,26        5           500             1037,28000000000
  LC              03/02/2014      52          200     0       1 035,04        6           300             1037,28000000000
  LF              03/02/2014      53          0       1040    1 356,44        7           1090            1356,48311926606 --((1357,38*50)+(1040*1356,44))/(1090)
  LF              03/02/2014      52          0       1560    1 045,00        8           1860            1043,75483870968 --((1037,28*300)+(1560*1045))/(1860)
  LC              04/02/2014      52          120     0       1 039,08        9           1740            1043,75483870968
  LC              04/02/2014      53          100     0       1 358,95        10          990             1356,48311926606
  LF              04/02/2014      52          0       600     1 038,71        11          2340            1042,46129032258 --((1043,75483870968*1740)+(600*1038,71))/(2340)
  LF              04/02/2014      53          0       1040    1 363,30        12          2030            1359,97000000000 --((1356,48311926606*990)+(1040*1363,3))/(2030)
  LC              05/02/2014      52          100     0       1 037,78        13          2240            1042,46129032258
  LF              15/03/2014      53          0       20      1 365,87        14          2050            1360,03301857239 --((1359,97551136621*2030)+(20*1365,87))/2050
  LF              15/03/2014      52          0       50      1 054,19        15          2290            1042.71737568672 --((1042.46129032258*2240)+(50*1054.19))/2290
  </code></pre>
  
  <p><strong>There is only one and just one document type <code>'SI'</code> (initial stock) for each product, and the price associated with it is the <code>initial WAC</code>.</strong></p>
  
  <p>Here is a <a href="http://www.sqlfiddle.com/#!15/ab373" rel="nofollow">SQL Fiddle</a> sample.</p>
  
  <p>If someone can help with this, i can't figure it out.</p>
  
  <p><strong>Edit</strong>: I've juste updated the calculated numbers by increasing precision by displaying 9 numbers after the decimal point.</p>