Postgresql return values on a reducing balance

Question

I have a query that returns items a person has paid for, the price of that item and the balance. My problem is there are situations where a person may make 2 payments for one item (make a payment, and another payment of the balance later). So the returned table looks like this:

 name    | invoice_id| inv_date | item_id|item     | pay_id | price | paid | balance |
---------|-----------|----------|--------|---------|--------|-------|------|---------|
 John Doe| 581       | 2018-4-10| 10     |Transport| 1165   | 8100  | 5400 | 2700    |
 John Doe| 581       | 2018-4-10| 10     |Transport| 1030   | 8100  | 2700 | 5400    |
 John Doe| 581       | 2018-4-10| 25     |Insurance| 1165   | 24000 | 12000| 12000   |
 John Doe| 581       | 2018-4-10| 25     |Insurance| 1030   | 24000 | 12000| 12000   |
---------|-----------|----------|--------|---------| -------|-------|------|---------|

As you can see from the return results, the balance for each second payment is incorrect. Take the second payment of transport for instance, there's a previous payment of 5400 and the current payment is 2700 so (5400 + 2700 = 8100 ) so the balance should be 0. So my question is how can I return the proper balance for each subsequent payment of each item?

Currently I'm returning the balance as (price - paid) but for subsequent payments the balance is incorrect.

Answer 1

you want a window function.

 price - 
    sum(paid) over (partition by item_id order by pay_id desc)
    as balance.

you probably want a different order by, use whatever ordering your report uses.