CODEWITHSUNDEEP
oracle-databasefunctionsumcumulative-sumanalytical
Marko
Marko

Reputation: 1000

Preventing running total from going negative in Oracle

Column 'amount' has value 5 in first row, and value -10 in second row.

Is there a way to make oracle's sum(amount) over() function to return 0 instead of -5 for the second row?

Upvotes: 1

Views: 842

Answers (2)

xQbert
xQbert

Reputation: 35333

Blatantly using Rajesh Chamarthi's example source: but altering to show more negative and positive... and showing how a case would change all the negative to zero while maintaining the other amounts...

with t as (
  select 5 as x, 1 as id from dual
  union all
  select -10, 2 as id from dual
  union all
  select 7, 3 as id from dual
  union all
  select -5, 4 as id from dual
  union all
  select -2, 5 as id from dual
),  
B as (select  t.x, 
        case when sum(x) over (order by id) < 0 then 0 
              else sum(x) over (order by id) 
        end Amount
 from t
 order by id)
 Select X, Case when amount < 0 then 0 else amount end as Amount from B;

  T   Amount
  5      5
-10      0
  7      2
 -5      0
 -2      0

----Attempt 2 (1st attempt preserved as comments below reference it)

I couldn't figure out how to interrupt the window function to reset the value to 0 when amount fell below 0... so I used a recursive CTE which gave me greater control.

If id's are not sequential, we could add a row_Number so we have an ID to join on... or we could use min() where > oldID. I assumed we have a single key unique ID or some way of "Sorting" the records in the order you want the sum to occur...

with aRaw as (
  select 5 as x, 15 as id from dual
  union all
  select -10, 20 as id from dual
  union all
  select 7, 32 as id from dual
  union all
  select 2, 46 as id from dual
  union all
  select -15, 55 as id from dual
  union all
  select 3, 68 as id from dual
),
t as (Select A.*, Row_number() over (order by ID) rn from aRAW A),

CTE(RN, ID, x, SumX) AS (
Select T.RN, T.ID, x, X from t WHERE ID = (Select min(ID) from t)
UNION ALL
Select T.RN, T.ID, T.X, case when T.X+CTE.SumX < 0 then 0 else T.X+Cte.sumX end from T
INNER JOIN CTE
 on  CTE.RN+1=T.RN)

Select * from cte;

.

  1. CTE: ARaw is just a sample data set
  2. CTE: T adds a sequental row number incase there are gaps in the IDs allowing for a more simple joining approach on the recursive CTE.
  3. CTE: CTE is the recursive CTE that keeps a running total and has a case statement to reset the running total to 0 when it falls below 0

Upvotes: 2

Rajesh Chamarthi
Rajesh Chamarthi

Reputation: 18808

You could use a case statement, but that would not be a true running total

with t as (
  select 5 as x, 1 as id from dual
  union all
  select -10, 2 as id from dual
  union all
  select 20, 3 as id from dual
  union all
  select 30, 4 as id from dual
  union all
  select 10, 5 as id from dual
)  
select  t.x, 
        case when sum(x) over (order by id) < 0 then 0 
              else sum(x) over (order by id) 
        end running_total
 from t
 order by id;

X   RUNNING_TOTAL

5   5
-10 0
20  15
30  45
10  55

Upvotes: 1

Related Questions