Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question.

Asked: October 05, 2016 - 2:33 pm UTC

Last updated: October 07, 2016 - 3:12 am UTC

Version: 11g

Viewed 10K+ times! This question is

You Asked

Hi,

Need some expertise here. Im trying to fetch cumulative sum from a transaction table downwards starting from a outstanding balance that I have handy. Below is the output (trying to get running total based on the calcn formula) that Im trying to get,

TRANS ID TRANS_DATE AMOUNT Running Total calcn (not part of output)
10 10/4/2016 8:44 100 200
9 10/4/2016 1:20 50 100 200-100
8 10/4/2016 0:06 -10 50 100-50
7 10/3/2016 13:09 20 60 50-(-10)

I have the outstanding balance 200 and i'm trying to do a top down approach starting with 200 (which is not a derived value).

I tried using sum(amount) but unable to use it with lag.

select
transid, trans_date, 200- (SUM (amount)
OVER (ORDER BY trans_date DESC, transid DESC
ROWS UNBOUNDED PRECEDING))
"Running Total"
from trantable
order by trans_date desc

could you please provide some suggestions?

and Chris said...

So you want 200 minus the running total from the previous rows?

If so basic analytics will do the job! Just supply a windowing clause to sum(). To get everything up to the previous row, use:
rows between unbounded preceding and 1 preceding

The first row will return null, so you need to nvl/coalesce it. This gives you:

create table t (
  id  int, 
  dt  date,
  amt int
);

insert into t values (1, date'2016-10-01', 100);
insert into t values (2, date'2016-10-02', 50);
insert into t values (3, date'2016-10-03', -10);
insert into t values (4, date'2016-10-04', 20);

select t.*, 
       200-coalesce(
         sum(amt) over (
           order by dt rows between unbounded preceding and 1 preceding
         ), 
         0
       ) tot 
from   t;

ID  DT         AMT  TOT
1   01-OCT-16  100  200
2   02-OCT-16  50   100
3   03-OCT-16  -10  50
4   04-OCT-16  20   60

Rating

  (3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

yet to get teh desired output

Mani, October 06, 2016 - 7:28 am UTC

-- Thanks for the prompt reply Chris. Believe I have not clearly stated my need.

create table t (
id int,
dt date,
amt int
);

insert into t values (1, date'2016-10-01', 100);
insert into t values (2, date'2016-10-02', 50);
insert into t values (3, date'2016-10-03', -10);
insert into t values (4, date'2016-10-04', 20);
commit;

select t.*,
200-coalesce(
sum(amt) over (
order by dt rows between unbounded preceding and 1 preceding
),
0
) tot
from t;

-- Below is what I received.

ID DT AMT TOT
1 10/1/2016 100 200
2 10/2/2016 50 100
3 10/3/2016 -10 50
4 10/4/2016 20 60



select sum(amt) from t; -- 160 is my total balance


-- here is the normal running total for all rows

select t.*, sum(amt) over (
order by dt rows unbounded preceding
) "Running total" from t order by dt desc;

ID DT AMT Running total
4 10/4/2016 20 160
3 10/3/2016 -10 140
2 10/2/2016 50 150
1 10/1/2016 100 100

-- now Im trying to derive the running balance from this value 160 in REVERSE (note: I will not do a sum(amt) to get total balance.Instead I will refer it from a different table may be using a subquery.

-- say I want to display the trxns >= 10/03 with running total.


select t.*, sum(amt) over (
order by dt rows unbounded preceding
) "Running total" from t
where dt >= to_date('10/03/2016','MM/DD/YYYY')
order by dt desc;

-- the above query will not give correct running balance as it filters trxns. Below is how it looks

ID DT AMT Running total
4 10/4/2016 20 10
3 10/3/2016 -10 -10

-- what Im trying to get is

ID DT AMT Running total
4 10/4/2016 20 160
3 10/3/2016 -10 140


-- In other words, I will be using the 160 for first row alone and subsequent will be cumulative based on amt. Hope I have explained clearly. is it possible to achieve this?
Connor McDonald
October 06, 2016 - 10:41 am UTC

So you just want a normal running total and then filter the results?

If so, first calculate the whole total, then apply your restrictions in an outer query:

select * from (
select t.*, 
       sum(amt) over ( 
         order by dt 
       ) tot 
from t
)
where  dt >= date'2016-10-03'
order  by dt desc;

        ID DT               AMT        TOT
---------- --------- ---------- ----------
         4 04-OCT-16         20        160
         3 03-OCT-16        -10        140

Note this requires you to scan the whole data set before filtering. So this could be slow if you're calculating the total over a large number of transactions.

If I understand correctly...

Stew Ashton, October 06, 2016 - 3:28 pm UTC

...the OP wants 160 minus the cumulative amount (except for the amount in the current row).
select id, dt, amt,
160 + amt - sum(amt) over (order by dt desc) cum_amt
from t
where dt >= date'2016-10-03';

        ID DT                AMT    CUM_AMT
---------- ---------- ---------- ----------
         4 2016-10-04         20        160
         3 2016-10-03        -10        140

Chris Saxon
October 07, 2016 - 3:12 am UTC

Thanks for the input Stew,

Cheers,
Connor

It worked

A reader, October 07, 2016 - 10:50 am UTC

This is what I was looking for. I was able to calculate the revers e cumulative sum but was struck in getting the op (160) on first row. Finally, the '160+amt' - sum(amt) did the magic.

I think this will be effective with respect to performance as well.

Thanks a lot Chris and Stew!
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Analytics

Analytic SQL got you confused? Check out Connor McDonald's complete video course.