How to create a cumulative column based on another column

Question

I am extremely new to SQL Server 2012. I would like to create a new cumulative column however this column is only to be summed up for the distinct names.

For instance if i have the names with values below, i would like the repeated the corresponding values of the repeated names to be cumulatively summed up:

James        200
Vicky        300
Jane         600
James        400
Vicky        700

The end goal of the new column should have values that look like the values column below:

James     200
Vicky     300
Jane      600
James     600
Vicky     1000

If you need the cumulative sum, we need another column with the order of the rows, — Lamak, Sep 04 '17 at 17:17
Try this you might be able to achieve by adding name field on group by https://stackoverflow.com/questions/2120544/how-to-get-cumulative-sum — sawbeanraz, Sep 04 '17 at 17:20

Ilyes · Accepted Answer · 2017-09-04T17:51:50.593

Try this:

DECLARE @T TABLE (Names VARCHAR(50), Value INT);

INSERT INTO @T VALUES
    ('James', 200),
    ('Vicky', 300),
    ('Jane', 600),
    ('James', 400),
    ('Vicky', 700);

SELECT Names, SUM(Value) OVER (PARTITiON BY Names ORDER BY Value) Value
FROM @T
ORDER BY Value;

Result:

+=======+=======+
| Names | Value |
+=======+=======+
| James |   200 |
+-------+-------+
| Vicky |   300 |
+-------+-------+
| James |   600 |
+-------+-------+

Demo

Note: It's not in the same order, if you want the same order, you should do what @Lamak say in his comment, because we need that column.

How to create a cumulative column based on another column

1 Answers1