CODEWITHSUNDEEP
sqldatabasepostgresql
Diand
Diand

Reputation: 888

PostgreSQL new Row based on previous Record Result

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

Upvotes: 0

Views: 32

Answers (1)

Popeye
Popeye

Reputation: 35930

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

Upvotes: 2

Related Questions