# Calculate cumulative rate with reset SQL Sever

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

### 1 Answer

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.

Gordon Linoff
posted this

## Have an answer?

JD

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