0

The idea is that I have one column Amount which holds both negative and positive numbers. I would like to calculate Balance for each row that will have the the previous amount plus the value inside Amount for example

| Amount |   | Balance | 
+--------+   |---------+
|   +5   |   |     5   | -- Initial balance should be 0
|   +100 |   |   105   |
|   -10  |   |    95   |

What I would like to ask is what should be the method or the algorithm used to calculate Balance?

Hatik
  • 1,139
  • 1
  • 15
  • 33

1 Answers1

2

You are looking for a cumulative sum:

select amount, sum(amount) over (order by ??)
from t;

The ?? is for the column that specifies the ordering. SQL tables represent unordered sets. So, for a cumulative sum, you need a column that specifies the ordering.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Indeed 'Cumulative sum' was the term I was looking for, totally forgot about the name of this term, thanks – Hatik Jul 02 '17 at 15:59