CODEWITHSUNDEEP
sqlsql-servert-sqlsumwindow-functions
Master
Master

Reputation: 11

Get the rolling SUM of one column and bring the result in SQL

I have a table with 3 columns:

ID   date           Amount
---------------------------
0    2019-01-25        500
3    2019-02-25       -200
4    2019-02-28        700
5    2019-02-28       -300

I want a query that returns the rolling total of the Amount column in an additional column:

ID   date           Amount     total amount
--------------------------------------------
0    2019-01-25        500              500
3    2019-02-25       -200              300
4    2019-02-28        700             1000
5    2019-02-28       -300              700

This is my query:

   SELECT 
       ID,
       TrDate,
       Amount,
       SUM(CONVERT(FLOAT, [Amount])) AS 'total amount'                     
   FROM 
       [Transactions]

Upvotes: 1

Views: 356

Answers (1)

GMB
GMB

Reputation: 222412

Use window function SUM(...) OVER(...):

SELECT *, SUM(Amount) OVER(ORDER BY id) total_amount FROM mytable;

Demo on DB Fiddle:

<pre>
ID | date                | Amount | total_amount
-: | :------------------ | -----: | -----------:
 0 | 25/01/2019 00:00:00 |    500 |          500
 3 | 25/02/2019 00:00:00 |   -200 |          300
 4 | 28/02/2019 00:00:00 |    700 |         1000
 5 | 28/02/2019 00:00:00 |   -300 |          700
</pre>

NB : if your version of SQLServer does not support window functions, then you can self-join the table and use aggregation:

SELECT t.id, t.date, t.amount, SUM(t1.amount) total_amount
FROM mytable t
INNER JOIN mytable t1 ON t1.id <= t.id
GROUP BY t.id, t.date, t.amount

Upvotes: 3

Related Questions