Sum of previous rows using two columns-CodePudding

I have a table name "Total_c" like that :

 id   bill    a   b

  1     a     0   10
  2     a     0   5
  3     a     0   3
  4     a    11   0
  5     a    13   0
  6     a     2   2

I need the result to be like that:

 id   bill    a   b     c

  1     a     0   10    10 
  2     a     0   5     15 
  3     a     0   3     18
  4     a    11   0     7
  5     a    13   0     6-  
  6     a     2   2     6-

I tried to do this query but I can not subtract a-b with previous row :

select 
  a, b,
  sum(b) OVER (PARTITION BY bill ORDER BY id ROWS 1 PRECEDING) 
 
 from Total_c

This query working just for one column but in my case not working, need to make change on this query , thanks all and I appreciate your help .

CodePudding user response：

You need to use the windowed SUM() with the appropriate window frame and the correct calculation. With ROWS 1 PRECEDING in the OVER clause the windowed function operates on the current and the preceding rows.

SELECT id, bill, a, b, SUM(b-a) OVER (PARTITION BY bill ORDER BY id) AS c
FROM Total_c

CodePudding user response：

Try this

drop table if exists #have;

create table #have
(
  ID    [int]
, bill  [Varchar](8)
, a     [int]
, b     [int]
)
;

insert into #have
values
  (1, 'a', 0 , 10)
, (2, 'a', 0 , 5 )
, (3, 'a', 0 , 3 )
, (4, 'a', 11, 0 )
, (5, 'a', 13, 0 )
, (6, 'a', 2 , 2 )
;

select a
     , b
     , sum(b - a) OVER (PARTITION BY bill ORDER BY id) as c
from #have
;