Follow

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use
Contact

Sum of previous rows using two columns

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 :

MEDevel.com: Open-source for Healthcare and Education

Collecting and validating open-source software for healthcare, education, enterprise, development, medical imaging, medical records, and digital pathology.

Visit Medevel

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 .

>Solution :

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
Add a comment

Leave a Reply

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use

Discover more from Dev solutions

Subscribe now to keep reading and get access to the full archive.

Continue reading