Sum running total in sql

Question

I am trying to insert a running total column into a SQL Server table as part of a stored procedure. I am needing this for a financial database so I am dealing with accounts and departments. For example, let's say I have this data set:

Account |  Dept  |   Date     |   Value  | Running_Total
--------+--------+------------+----------+--------------
 5000   |  40    | 2018-02-01 |     10   |      15
 5000   |  40    | 2018-01-01 |     5    |      5
 4000   |  40    | 2018-02-01 |     10   |      30
 5000   |  30    | 2018-02-01 |     15   |      15
 4000   |  40    | 2017-12-01 |     20   |      20

The Running_Total column provides a historical sum of dates less than or equal to each row's date value. However, the account and dept must match for this to be the case.

I was able to get close by using

SUM(Value) OVER (PARTITION BY Account, Dept, Date)

but it does not go back and get the previous months...

Any ideas? Thanks!

Gordon Linoff · Accepted Answer · 2019-04-17 01:35:54Z

1

You are close. You need an order by:

Sum(Value) over (partition by Account, Dept order by Date)

answered Apr 17, 2019 at 1:35

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Sum running total in sql

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related