title: Calculate out price in FIFO SQL
body: <p>Using Postgres 11</p>
  
  <p>Using FIFO, i would like to calculate the price of items taken from the inventory, to keep track of the value of the total inventory.</p>
  
  <p>Dataset is as follows: </p>
  
  <pre><code>ID  | prodno | amount_purchased | amount_taken | price | created_at
  uuid  13976    10                 NULL           130     &lt;timestamp&gt;  
  uuid  13976    10                 NULL           150     &lt;timestamp&gt;
  uuid  13976    10                 NULL           110     &lt;timestamp&gt;
  uuid  13976    10                 NULL           100     &lt;timestamp&gt;
  uuid  13976    NULL                 14           ??      &lt;timestamp&gt;
  </code></pre>
  
  <p>Before inserting the row with <code>amount_taken</code> i would need to calculate what the avg price of each of the 14 items is, which in this case would be <code>135,71</code>, but how to calculate this relatively efficient?</p>
  
  <p>My initial idea was to delegate the rows into two temp tables, one where amount_taken is null, and one where it is not null, and then calculate all the rows down, but seeing as this table could become rather large, rather fast (since most of the time, only 1 item would be taken from the inventory), i worry this would be a decent solution in the short term, but would slow down, as the table becomes larger. So, what's the better solution internet?</p>