How can i do cumulative total in SQL Server?

Question

Company_Name   Amount        Cumulative Total
---------------------------------------------
Company 6         100             100
Company 6         200             300
Company 6         150             450
Company 7         700             700
Company 7        1100            1800
Company 7         500            2300

How can I do cumulative sum group by company as shown in this example?

(1) `0` is not a cumulative sum. (2) SQL table represent *unordered* sets. The ordering needs to be specified by a column. — Gordon Linoff, Jun 22 '21 at 12:19
I have never seen a cumulative sum question that has `0` for the first row. — Gordon Linoff, Jun 22 '21 at 12:20
How is this *not* a duplicate of [How to get cumulative sum](https://stackoverflow.com/questions/2120544/how-to-get-cumulative-sum) @GordonLinoff ...With respect, as someone who is yet to (work out how to) use the dupe hammers you have access to, I don't appreciate you reverting mine... The dupe candidate shows the OP *exactly* how to do a cumulative `SUM`... — Thom A, Jun 22 '21 at 12:22
Does this answer your question? [How to get cumulative sum](https://stackoverflow.com/questions/2120544/how-to-get-cumulative-sum) — Thom A, Jun 22 '21 at 12:24
Does this answer your question? [Calculate a Running Total in SQL Server](https://stackoverflow.com/questions/860966/calculate-a-running-total-in-sql-server) — Thom A, Jun 22 '21 at 12:24
Does this answer your question? [How to get cumulative sum](https://stackoverflow.com/questions/2120544/how-to-get-cumulative-sum) — Charlieface, Jun 22 '21 at 13:11
@GordonLinoff OP has now changed the question, so it's just a regular cumulative sum — Charlieface, Jun 22 '21 at 13:11
@Charlieface unless you give explicit instructions, Gordon has no idea how to use the "Close as duplicate" feature. ;) — Thom A, Jun 22 '21 at 19:36

score 1 · Accepted Answer · answered Jun 22 '21 at 12:17

First, you need a column that specifies the ordering, because SQL tables represent unordered sets. Let me assume you have such a column.

Then the function is sum() as a window function:

select t.*,
       sum(amount) over (partition by company order by <ordering col>)
from t;

Note: This does not return 0 for the "first" row for each company, so it really is a cumulative sum. For your logic, you need an additional conditional:

select t.*,
       (case when row_number() over (partition by company order by <ordering col>) = 1
             then 0 
             else sum(amount) over (partition by company order by <ordering col>)
        end)
from t;

How can i do cumulative total in SQL Server?

1 Answers1