CODEWITHSUNDEEP
sqlpostgresql
Athish
Athish

Reputation: 11

Postgresql Subtracting two columns data and pass that value to next row

i have two columns 'opening balance' and 'issue', 'balance' is 'opening balance' - 'issue'

Table I have

enter image description here

I need to pass the subtracted 'balance' to pass to next row and so on

Expected result

enter image description here

Is there any solution ?

Upvotes: 0

Views: 723

Answers (1)

Tomáš Záluský
Tomáš Záluský

Reputation: 12090

You are solving a variant of "running totals" problem. What is weird on your question is there is no criterion of row ordering - you should have some id or usually timestamp in your data to specify logic order of transactions.

You can solve problem using window functions, for example:

with t (order_criterion, opening,issue) as (values
  (1,1200,6),
  (2,1200,10),
  (3,1200,8),
  (4,1200,9)
)
select opening - coalesce(sum(issue) over (order by order_criterion rows between unbounded preceding and 1 preceding), 0) as opening
     , issue
     , opening - sum(issue) over (order by order_criterion) as closing
from t

or

with t (order_criterion, opening,issue) as (values
  (1,1200,6),
  (2,1200,10),
  (3,1200,8),
  (4,1200,9)
), i as (
  select order_criterion
       , opening
       , issue
       , opening - sum(issue) over (order by order_criterion) as closing
  from t
)
select coalesce(lag(closing) over (order by order_criterion), opening) as opening
     , issue
     , closing
from i

See db fiddle: https://dbfiddle.uk/?rdbms=postgres_12&fiddle=53705b0d971deb9395df2091fba0857e

Upvotes: 1

Related Questions