CODEWITHSUNDEEP
sqlt-sql
beginner1234
beginner1234

Reputation: 19

How to calculate running total in sql backwards

enter image description here

select id,date,amount,SUM (Amount)  OVER (PARTITION BY ID ORDER BY DATE desc)  AS runningbalance from sales

I have tried this to reverse running balance going from the most recent to older records. How can I reverse this to go backwards to give me running balance the other way?

Upvotes: 0

Views: 403

Answers (3)

LukStorms
LukStorms

Reputation: 29677

Just to show an example of what the partition is used for.

declare @Sales table (
  ID int identity(1,1) primary key,
  [Date] date,
  Store varchar(30),
  Amount int
  
);

insert into @Sales ([Date], Store, Amount)
values
  ('2020-01-01','A',1), ('2020-01-03','A',1)
, ('2020-01-05','A',1)
, ('2020-01-02','B',10), ('2020-01-04','B',10)
, ('2020-01-06','B',10)
;
    
select Store, [Date], Amount, 
    sum(Amount) over (partition by Store order by [Date] ASC) as RunningTotal, 
    sum(Amount) over (partition by Store order by [Date] DESC) as ReverseRunningTotal
from @Sales
order by Store, [Date] 
GO

Store | Date       | Amount | RunningTotal | ReverseRunningTotal
:---- | :--------- | -----: | -----------: | ------------------:
A     | 2020-01-01 |      1 |            1 |                   3
A     | 2020-01-03 |      1 |            2 |                   2
A     | 2020-01-05 |      1 |            3 |                   1
B     | 2020-01-02 |     10 |           10 |                  30
B     | 2020-01-04 |     10 |           20 |                  20
B     | 2020-01-06 |     10 |           30 |                  10

Upvotes: 0

Michael Bruesch
Michael Bruesch

Reputation: 660

If your ID is unique to each row then remove the PARTITION BY ID else it won't sum cumulatively. To reverse the order of the running total simply reverse the ORDER BY of the SUM:

DDL:

declare @sales table (
    ID int,
    Date date,
    Amount int);

insert into @sales
values
    (1, '2020-01-01', 15),
    (2, '2020-01-02', 10),
    (3, '2020-01-03', 5);

DML:

select ID, Date, Amount, 
    sum(Amount) over (order by Date) as RunningBalanceForward, 
    sum(Amount) over (order by Date desc) as RunningBalanceBackwards
from @sales
order by ID

Results:

ID Date Amount RunningBalanceForward RunningBalanceBackwards
1 2020-01-01 15 15 30
2 2020-01-02 10 25 15
3 2020-01-03 5 30 5

Upvotes: 3

ListenSoftware Louise Ai Agent
ListenSoftware Louise Ai Agent

Reputation: 4253

in addition to the running total, use a row_number to create the sort column and partition it by id order by date desc generating a rowid then order by the rowid

Upvotes: 0

Related Questions