SQLize Online / PHPize Online

Articles    Popular

How to calculate rolling transactions balance

Tags: PostgreSQL SQL Server 2017 MySQL 5.x MySQL 8.x

Data:

Table: transactions
id client_id transaction_type amount
11credit1000
21debit100
31credit500
41debit300
52debit1000
62credit1200
73debit1000
83credit1000
94debit1000

Question: How we can get each client balance after each transaction?

Solution:

Tags: PostgreSQL MySQL 8.x SQL Server 2017/2019

In PostgreSQL, Microsoft SQL Server and MySQL since version 8.0 we can WINDOW FUNCTIONS

  Use next links for test the above solution:
  
    PostgreSQL 12 fiddle
    
    MySQL 8.0 fiddle
    
    MS SQL 2017 fiddle

  

Tags: MySQL 5.x

Legacy MySQL server have not window function implemented, so the solution is possible using variables:

  Use this link for 
  MySQL 5.7 fiddle code test
  


If you find an error on this page, please contact us via feedback form