CODEWITHSUNDEEP
sqlt-sql
PoNti3
PoNti3

Reputation: 41

Calculate Balance from Transactions

I have a table that looks like this 

ID  Type    Amount  Created
10  4   30,00   2019-11-29 11:34:54.417
1   1   10,50   2019-11-19 11:34:54.417
3   2   16,50   2019-11-17 11:34:54.417
2   4   11,50   2019-11-15 11:34:54.417
4   6   10,00   2019-11-11 11:34:54.417
5   3   8,60    2019-10-19 11:34:54.417
7   1   21,50   2019-05-19 11:34:54.417
8   4   9,00    2019-04-19 11:34:54.417
9   1   8,00    2019-02-19 11:34:54.417
6   1   1,50    2019-01-19 11:34:54.417

Imagine this table keeps an e-wallet and these are Transactions with ID , Type(withdrawals , reversals , deposits etc..) ,Amount and datetime Created. Lets say that all these 10 transactions refer to a specific Customer. Thus if i run 

SELECT SUM(Amount) AS Balance
FROM  transactions
WHERE Created <= '20191120'

this query will return the Balance of this customer until 2019/11/20.

What i want is to run a select query to this table and keep only the Transactions with Type=4. E.g.

SELECT  ID
    ,   Type
    ,   Amount
    ,   Created
FROM    transactions
WHERE type=4

This query returns the following 

ID  Type    Amount  Created
2   4   11,50   2019-11-15 11:34:54.417
8   4   9,00    2019-04-19 11:34:54.417
10  4   30,00   2019-11-29 11:34:54.417

What i really want though is an extra column in this result set that shows the balance of the customer at the point of each transaction(with Type=4). For example when he did the Transaction with ID = 2 His balance before this(not counting the current(id=2) was (1,50+8,00+9,00+21,50+8,60+10,00) , when he did the Transaction with ID = 8 his balance was (1,50+8,00) and so on..

A desired Result set would be 

  ID    Type    Amount  Created  Balance
2   4   11,50   2019-11-15 11:34:54.417  58,60
8   4   9,00    2019-04-19 11:34:54.417  9,50
10  4   30,00   2019-11-29 11:34:54.417  97,1

I want to do this in one Select Query. I have some thoughts of doing it in two steps but that's not my intention, i Just need to run it once and have all five desired columns.

Upvotes: 0

Views: 2122

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269445

You want a cumulative sum and filtering:

SELECT t.*
FROM (SELECT t.*, SUM(Amount) OVER (ORDER BY Created) - Amount AS Balance
      FROM  transactions t
      WHERE Created <= '20191120'
     ) t
WHERE type = 4;

Upvotes: 0

ravioli
ravioli

Reputation: 3823

Looking carefully at your desired output, assuming your DBMS supports window functions, you can do this using a pseudo-cumulative sum:

SELECT ID, Type, Amount, Created, Balance
FROM (
  SELECT ID, Type, Amount, Created, 
    SUM(Amount) OVER(-- Sum "amount" of all rows before current row (exclude current row)
      ORDER BY Created ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING 
  ) AS Balance
  FROM transactions
) src
WHERE type = 4
ORDER BY id
;

SQL Fiddle

Also, shouldn't the balance for id=2 be 58,6 and the balance for id=10 be 97.1?

Upvotes: 2

Related Questions