title: Create calculated field based on previous rows Oracle SQL
body: <p>I am trying to create a manually calculated column where I keep track of a current inventory.</p>
  
  <p>Currently, I have a table that looks like this:</p>
  
  <pre><code>| Group        | Part | Operation Type | Transaction Amount |
  |--------------|------|----------------|--------------------|
  | Concrete     | A    | STOCK          | 100                |
  | Concrete     | A    | Buy            | 25                 |
  | Concrete     | A    | Make           | -10                |
  | Concrete     | A    | Make           | -10                |
  | Concrete     | A    | Make           | -10                |
  | Concrete     | A    | Make           | -10                |
  | Concrete     | A    | Make           | -10                |
  | Concrete     | B    | STOCK          | -10                |
  | Concrete     | B    | Make           | -10                |
  | Concrete     | B    | Make           | -10                |
  | Concrete     | B    | Make           | -10                |
  | Concrete     | B    | Make           | -10                |
  | Concrete     | B    | Make           | 150                |
  | Construction | C    | STOCK          | 10                 |
  | Construction | C    | Make           | -1                 |
  | Construction | C    | Make           | -1                 |
  | Construction | C    | Make           | -1                 |
  | Construction | C    | Make           | -1                 |
  | Construction | D    | STOCK          | 5                  |
  | Construction | D    | Make           | -5                 |
  </code></pre>
  
  <p>The table is first ordered by <code>group</code> then by <code>part</code>, and then <code>STOCK</code> is always shown as the first value. The idea is to create a new manually calculated column, <code>curr_inventory</code>, that allows for us to keep track of current inventory and see if or when our inventory for a given part, for a given group, dips below 0.</p>
  
  <p>Ideally, the end results would look like this:</p>
  
  <pre><code>|     Group    | Part | Operation Type | Transaction Amount | New_Inventory_Column |
  |:------------:|:----:|:--------------:|:------------------:|:--------------------:|
  |   Concrete   |   A  |      STOCK     |         100        |          100         |
  |   Concrete   |   A  |       Buy      |         25         |          125         |
  |   Concrete   |   A  |      Make      |         -10        |          115         |
  |   Concrete   |   A  |      Make      |         -10        |          105         |
  |   Concrete   |   A  |      Make      |         -10        |          95          |
  |   Concrete   |   A  |      Make      |         -10        |          85          |
  |   Concrete   |   A  |      Make      |         -10        |          75          |
  |   Concrete   |   B  |      STOCK     |         10         |          10          |
  |   Concrete   |   B  |      Make      |         -10        |           0          |
  |   Concrete   |   B  |      Make      |         -10        |          -10         |
  |   Concrete   |   B  |      Make      |         -10        |          -20         |
  |   Concrete   |   B  |      Make      |         -10        |          -30         |
  |   Concrete   |   B  |      Make      |         150        |          120         |
  | Construction |   C  |      STOCK     |         10         |          10          |
  | Construction |   C  |      Make      |         -1         |           9          |
  | Construction |   C  |      Make      |         -1         |           8          |
  | Construction |   C  |      Make      |         -1         |           7          |
  | Construction |   C  |      Make      |         -1         |           6          |
  | Construction |   D  |      STOCK     |          5         |           5          |
  | Construction |   D  |      Make      |         -5         |           0          |
  </code></pre>
  
  <p>The end result would be a column that initiates when the part number has changed and the operation type is STOCK, and then begins to calculate (using the transaction amount) what the current inventory is.
  I am not sure where to start on a SQL query that would allow for this. Intuitively, the pseudocode would look something like:</p>
  
  <pre><code>for each row in table:
      if operation_type == "stock":
          curr_inv = stock.value
      else:
          curr_inv = previous_curr_inv + transaction_amount
  </code></pre>
  
  <p>However, I am not sure how to even begin writing SQL for this. I typically try to post what SQL I am working with but I don't even know where to begin. I have looked at various posts online, on SO, including posts <a href="https://stackoverflow.com/questions/37517940/sql-calculated-field-base-on-calculated-field">like this</a>, <a href="https://stackoverflow.com/questions/53268337/create-calculated-value-based-on-calculated-value-inside-previous-row">and this</a>, <a href="https://stackoverflow.com/questions/37776958/oracle-sql-calculate-value-using-previous-row">and this</a>, but I could not see how the selected answers could be used as a solution.</p>