SQL Running Total Using Two Columns

Question

I have the following sample dataset

ID	Type	Opening Balance	Trans Amount
1	Credit	1000.00	40.00
2	Debit	1000.00	-50.00
3	Debit	1000.00	-20.00
4	Credit	1000.00	10.00
5	Debit	1000.00	-30.00

The opening balance is a fixed amount from the previous day. What i want is to use it to start computing the running balance from the first row. The desired output should be as follows

ID	Type	Opening Balance	Trans Amount	Running Total
1	Credit	1000.00	40.00	1040.00
2	Debit	1000.00	-50.00	990.00
3	Debit	1000.00	-20.00	970.00
4	Credit	1000.00	10.00	980.00
5	Debit	1000.00	-30.00	950.00

The script i have written so far is only able to do a running total on the trans amount without factoring the opening balance on the first row

    SELECT SUM([trans amount]) OVER (PARTITION BY id ORDER BY id) from dbo.table1

How do i achieve the intended results

user12571241 · Accepted Answer · 2022-06-01 21:57:24Z

I was originally going the same direction as lemon's answer, but figured a way that you only add [Opening Balance] from the first row would be useful as well.

You can make a CTE with a case that will add the first row to get your new total, and then the rest of the original values, and then sum that new column:

with tbl as
(
    select ID
        , Type
        , [Opening Balance]
        , [Trans Amount]
        , case
            when ROW_NUMBER() over (order by ID) = 1 then [Opening Balance] + sum([Trans Amount]) over (order by ID)
            else [Trans Amount]
          end [New Trans Amount]
    from orig_tbl
)
select ID
    , Type
    , [Opening Balance]
    , sum([New Trans Amount]) over (order by ID) [Running Total]
from tbl

What's nice about this way is that your subsequent opening balance can vary without affecting [Running Total].

However, if [Opening Balance] does not vary, then lemon's version is simpler.

lemon · Accepted Answer · 2022-06-01 21:51:14Z

It is sufficient to add up the Opening Balance value to your window function result:

SELECT *,
       [Opening Balance] + SUM([Trans Amount]) OVER(ORDER BY [ID]) AS [Running Total]
FROM table1

Partitioning inside the window function is not necessary as long as you have unique values for the ID field (at least in the input sample).

Try it here.

Note: I have assumed you're using SQL Server by looking at your query. If that's not the case, please indicate it into your post as a tag.

Collectives™ on Stack Overflow

SQL Running Total Using Two Columns

2 Answers 2

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Related