SSMS SQL Need to increment a value for each row for a group

Question

I have the following table:-

policy number   transaction date   value      running total
123             26/01/2015         1000.00    0
123             13/02/2017         3000.00    0
234             14/02/2014          500.00    0
234             14/02/2016         4500.00    0
234             23/06/2018         1200.00    0
345             17/07/2017         7000.00    0

I want it to look like this:-

policy number   transaction date   value      running total
123             26/01/2015         1000.00    1000.00
123             13/02/2017         3000.00    4000.00
234             14/02/2014          500.00     500.00
234             14/02/2016         4500.00    5000.00
234             23/06/2018         1200.00    6200.00
345             17/07/2017         7000.00    7000.00

Have you tried a windowing function? Add your code attempt to your question. — Dan Guzman, Jul 14 '18 at 14:46
There is an extensive answer to the running total problem here https://stackoverflow.com/questions/11310877/calculate-running-total-running-balance Did you look at it? — Steve, Jul 14 '18 at 14:50
Just do this: `RunningTotal = SUM(value) OVER (PARTITION BY PolicyNumber)`. What that means in plain English: *Create groups of data by the PolicyNumber and keep a running total of the RunningTotal column for each group. Once the group changes (PolicyNumber changes), then start the total again.* — CodingYoshi, Jul 14 '18 at 15:07
@CodingYoshi, that produces 4000.00, 4000.00, 6200.00, 6200.00, 6200.00, 7000.00 — derloopkat, Jul 14 '18 at 15:19
Note: adding an attempt to a question is often very effective at stopping it being put on hold. As it is, this _looks like there was no attempt_, even if there was. Please add that as promptly as you can (even if your deadline has now passed). Thank you! — halfer, Jul 17 '18 at 18:10

score 1 · Answer 1 · answered Jul 14 '18 at 15:13

You are looking for the cumulative sum window function, available in SQL Server since SQL Server 2012:

select f.*,
       sum(f.value) over (partition by f.policy_number order by f.transaction_date) as running_total
from following f;

You can incorporate this into an update using an updatable CTE:

with toupdate as (
      select f.*,
             sum(f.value) over (partition by f.policy_number order by f.transaction_date) as new_running_total
      from following f
     )
update toupdate
    set running_total = new_running_total;

score 1 · Answer 2 · answered Jul 14 '18 at 15:50

You can use this query to achieve the desired result:

select 
    PolicyNumber, 
    TransactionDate, 
    [Value],
    RunningTotal = SUM([Value]) OVER (PARTITION BY PolicyNumber 
                                      order by (PolicyNumber) 
                                      rows unbounded preceding) 
from [dbo].[Q51340534]

What that means in plain English: Create groups of data by the PolicyNumber and keep a running total of the preceding Value column for each group. Once the group changes (PolicyNumber changes), then start the total again.

SSMS SQL Need to increment a value for each row for a group

2 Answers2