CODEWITHSUNDEEP
sqloracle-databasewindow-functionscumulative-sum
Jatin Garg
Jatin Garg

Reputation: 85

Calculate the Running Total logic but first cell is coming up blank

I need to create a running total for a report. I have tried using unbounded window function but my first cell is coming up blank. I need the same logic in the first cell too. Below is the initial amount I have in the table. I am using it to create the running amount as per reference number in descending order

Initial Amount = 995

Logic tried:

InitialAmount - SUM (Amount)  OVER(PARTITION BY ID Order By REFERENCE_NUMBER DESC ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING ) AS RUNNING_AMOUNT

Result:

enter image description here

Expected Result:

enter image description here

Upvotes: 0

Views: 38

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269623

I usually write this without the windowing clause as:

(InitialAmount -
 (SUM(Amount)  OVER (PARTITION BY ID Order By REFERENCE_NUMBER DESC) - Amount
 )
) AS RUNNING_AMOUNT

That is, it just does a regular cumulative sum (including the current row) and subtracts out the current value).

Upvotes: 0

GMB
GMB

Reputation: 222432

The problem is that your window is empty for the first row, so the sum() yields null, which propagates to the result of the substraction.

You can work around this with coalesce():

InitialAmount 
    - COALESCE(
        SUM (Amount) OVER(
            PARTITION BY ID 
            Order By REFERENCE_NUMBER DESC 
            ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
        ),
        0
    ) AS RUNNING_AMOUNT

Upvotes: 1

Avi
Avi

Reputation: 1845

You can try adding isnull, 0 I guess that may be a problem here. 

InitialAmount - isnull(SUM (Amount)  OVER(PARTITION BY ID Order By REFERENCE_NUMBER 
DESC ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING ),0) AS RUNNING_AMOUNT

Upvotes: 0

Related Questions