Calculate cumulative rate with reset SQL Sever

1975 views sql
6

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.

Alex

answered question

Please edit your question and provide sample data and desired results.

1 Answer

12

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.

posted this

Have an answer?

JD

Please login first before posting an answer.