# Get the difference for a column value by all its previous rows data

## All we need is an easy explanation of the problem, so here it is.

1. Vault

• id BIGSERIAL PRIMARY KEY
• name VARCHAR(30) NOT NULL
2. Transaction

• id BIGSERIAL PRIMARY KEY
• transaction_type VARCHAR(6) NOT NULL — Choice between "credit" or "debit"
• amount NUMERIC(15,6) NOT NULL
• vault_id BIGINT REFERENCES Vault (id)

When getting Transaction row/s, I would like to get an extra column for the remaining vault balance after the transaction is done.

Example of Transaction:

``````id      transaction_type        amount      vault_id        vault_balance
1       credit                  100         1               100 // since there was no prev transaction for this vault
5       credit                  400         1               500
12      debit                   200         1               300
20      credit                  100         1               400
``````

I can add a column to hold the vault balance and use that for the next transaction. But suppose the user deletes a row, for example row id 5 which has credit amount of 400 is deleted. Then its following transactions vault balance should change accordingly. So for row 12 the vault balance would be -100, and row id 20’s vault balance would be 0.

``````id      transaction_type        amount      vault_id        vault_balance
1       credit                  100         1               100 // since there was no prev transaction for this vault
12      debit                   200         1               -100
20      credit                  100         1               0
``````

The way I see it I have two ways:

• Add a column to store the vault balance in a transaction, and if there is a change, all of its following transaction’s vault balance should be updated accordingly.
• Maybe there’s a way to get the difference of credit and debit between all of its previous and the current transaction amount.

But I am hoping there is a much better way? Could you please help me what would be most efficient way to handle such situation?

Update

## How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

### Method 1

Something like this, may be?

``````sum(case transaction_type when 'debit' then -1 else 1 end * amount)
over (partition by vault_id order by id rows unbounded preceding and current row)
``````

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂