Here is an example inspired by
Calculating simple running totals in SQL Server[
^] . I think your example of expected output is wrong. It should be 20, 60, 60*, 120
DECLARE @RunTotalTestData TABLE (
id int not null identity(1,1) primary key,
value int null
);
INSERT INTO @RunTotalTestData (value) VALUES (1);
INSERT INTO @RunTotalTestData (value) VALUES (2);
INSERT INTO @RunTotalTestData (value) VALUES (4);
INSERT INTO @RunTotalTestData (value) VALUES (7);
INSERT INTO @RunTotalTestData (value) VALUES (9);
INSERT INTO @RunTotalTestData (value) VALUES (12);
INSERT INTO @RunTotalTestData (value) VALUES (NULL);
INSERT INTO @RunTotalTestData (value) VALUES (16);
INSERT INTO @RunTotalTestData (value) VALUES (22);
INSERT INTO @RunTotalTestData (value) VALUES (42);
INSERT INTO @RunTotalTestData (value) VALUES (57);
INSERT INTO @RunTotalTestData (value) VALUES (NULL);
INSERT INTO @RunTotalTestData (value) VALUES (59);
INSERT INTO @RunTotalTestData (value) VALUES (60);
;WITH temp AS(
SELECT a.id, a.value, SUM(b.Value) 'Sum'
FROM @RunTotalTestData a,
@RunTotalTestData b
WHERE b.id <= a.id
GROUP BY a.id, a.value
)
SELECT id,
CASE
WHEN value is NULL THEN CONVERT(VARCHAR(5),[Sum]) +'*'
ELSE CONVERT(VARCHAR(5),[Sum])
END 'Quantity'
FROM temp
ORDER BY id
Output:
id Quantity
1 1
2 3
3 7
4 14
5 23
6 35
7 35*
8 51
9 73
10 115
11 172
12 172*
13 231
14 291