Running total in SQL Server based on condition

Question

I am creating a running total for specific group in a sequence. In between a sequence zero value occurs for which I have to start the running total from the zero record

select 
    Sno,
    Group,
    Value,
    sum(Value) over(partition by Group order by Sno) Cum_Value 
from 
    Table

Output:

Sno Group   Value   CumValue
-------------------------------
1   A   5   5
2   A   10  15
3   A   25  40
4   A   0   40
5   A   10  50
6   A   5   55
7   A   0   55
7   A   20  75

Sno Group   Value   CumValue
------------------------------
1   A   5   5
2   A   10  15
3   A   25  40
4   A   0   0--> zero occurs [starts running total again]
5   A   10  10
6   A   5   15
7   A   0   0--> zero occurs [starts running total again]
7   A   20  20

Yes,since my value is 0 my cum value starts from this record — Kirthiga, Apr 22 '19 at 06:24
Just write a case then, Case when Value is 0 then 0 else CumValue end as CumValue — Praneet Nadkar, Apr 22 '19 at 06:25
next record of 0 value sholud be the sum of 0 and current record not the whole cumulative — Kirthiga, Apr 22 '19 at 06:27

score 2 · Accepted Answer · answered Apr 22 '19 at 06:31

You may try with the following approach:

Input:

CREATE TABLE #Data (
    Sno int, 
    [Group] varchar(1),   
    [Value] int
)
INSERT INTO #Data 
   (Sno, [Group], [Value])
VALUES
    (1, 'A', 5), 
    (2, 'A', 10), 
    (3, 'A', 25), 
    (4, 'A', 0),  
    (5, 'A', 10), 
    (6, 'A', 5),  
    (7, 'A', 0),  
    (8, 'A', 20)

Statement:

SELECT
    Sno, 
    [Group], 
    [Value],
    Changed,
    SUM([Value]) OVER (PARTITION BY Changed ORDER BY Sno) AS Cum_Value
FROM 
    (
    SELECT 
        Sno, 
        [Group], 
        [Value],
        SUM (CASE 
            WHEN [Value] = 0 THEN 1
            ELSE 0
        END) OVER (PARTITION BY [Group] ORDER BY Sno) AS Changed
    FROM #Data
    ) t

Output:

Sno Group   Value   Cum_Value
1   A       5       5
2   A       10      15
3   A       25      40
4   A       0       0
5   A       10      10
6   A       5       15
7   A       0       0
8   A      20       20

Running total in SQL Server based on condition

1 Answers1