PostgreSQL new Row based on previous Record Result

Question

is it possible to create a new field ballance(see the expected result in the bottom) from query using this table?

check: https://dbfiddle.uk/?rdbms=postgres_12&fiddle=8ed42ebefc7390b152d293ec1176f7c0

Table Transaction:

id      Usage         take  give 
------  ------------  ----  ----  
  1     Selling AAA   10    0         
  2     Purchase 1    0     40    
  3     Selling BBB   50    0

so the ballance on 1st record is ballance = take(10) - give(0) = 10

ballance of the 2nd record is, ballance = 1st record ballance(10) + take(0) - give(40) = -30

ballance of the 3rd record is, ballance = 2nd record ballance(-30) + take(50) - give(0) = 20

Output expected order by id:

id      Usage         take  give  ballance
------  ------------  ----  ----  --------
  1     Selling AAA   10    0     10    
  2     Purchase 1    0     40    -30
  3     Selling BBB   50    0     20

Please tell us what you want to achieve: Do you want to add a column to the table with the calculated data? Maybe you should think about views (or materialized views). Or use the new feature "derived column". So, you don't need to store the data twice and avoid data inconsistency. — S-Man
– S-Man, Commented Dec 15, 2020 at 9:32
thankyou for the respond, i think @Popeye already answered the question as expected... — Diand
– Diand, Commented Dec 15, 2020 at 9:34

Popeye · Accepted Answer · 2020-12-15 09:24:34Z

2

You can use the SUM analytical function as follows:

select t.*,
       sum(take - give) over (order by id) as balance
from transaction t

db<>fiddle

answered Dec 15, 2020 at 9:24

Popeye

36k4 gold badges12 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Diand Over a year ago

Woah, i didn't expect that the query can be this short...Working as expected! thanks!

Collectives™ on Stack Overflow

PostgreSQL new Row based on previous Record Result

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related