I have created a excel workbook that has the formula I require but I now need to do the same in sql.
I have three columns; Analysis, loss, rate.
In excel I have two additional columns; Rank - sequential numbers starting a 1 for every analysis. Cumulative rate - running total sum of the rate, resetting when the rank = 1
Can someone please explain how this can be done in sql server?
Many thanks in advance and sorry if this question has been asked before, I was struggling to follow the answers to similar questions.
You can calculate the cumulative sum using a window function:
select t.*, sum(t.rate) over (partition by t.analysis order by t.rank) as cumulative_rate from t;
It is unclear from your question how
rank is calculated. In SQL, tables represent unordered sets. Hence, you need a column that specifies the ordering for operation.