Question 21310 in Mysql Question posted in
The official MySQL documentation can be found here.

Running balance calculation using window functions with join – Mysql

I am trying to find a way to calculate the running balance of an account.
Below is my schema and the query I tried.

CREATE TABLE transactions(
   id   INTEGER  NOT NULL PRIMARY KEY 
  ,date DATE  NOT NULL
  ,dr   INTEGER  NOT NULL
  ,cr   INTEGER  NOT NULL
  ,amt  INTEGER  NOT NULL
);
CREATE TABLE accounts(
   id   INTEGER  NOT NULL PRIMARY KEY 
  ,name VARCHAR(55)  DEFAULT NULL
);
INSERT INTO accounts(id,name) VALUES (1,'C1');
INSERT INTO accounts(id,name) VALUES (2,'C2');
INSERT INTO accounts(id,name) VALUES (3,'C3');

INSERT INTO transactions(id,date,dr,cr,amt) VALUES (1,'2020-01-01',1,2,100);
INSERT INTO transactions(id,date,dr,cr,amt) VALUES (2,'2020-01-01',1,2,200);
INSERT INTO transactions(id,date,dr,cr,amt) VALUES (3,'2020-01-02',1,3,100);
INSERT INTO transactions(id,date,dr,cr,amt) VALUES (4,'2020-01-03',3,2,100);

SELECT date, dr as id, balance FROM (
  SELECT date, dr, SUM(amt) OVER (PARTITION BY dr ORDER BY date ) as balance FROM transactions
  GROUP BY date, dr, amt
  UNION ALL
  SELECT date, cr, -SUM(amt) OVER (PARTITION BY cr ORDER BY date ) as balance FROM transactions
  GROUP BY date, cr, amt
) as balances
GROUP BY date, dr, balance
ORDER BY date, dr, balance

Output

date id balance
2020-01-01 1 300
2020-01-01 2 -300
2020-01-02 1 400
2020-01-02 3 -100
2020-01-03 2 -400
2020-01-03 3 100

Trying to arrive at the output as shown below, taking into consideration of debit and credit accounts. But as you can see, the balance of C3 on 2020-01-03 is 100 instead of 0. What am I doing wrong?

id name date balance
1 C1 2020-01-01 300
2 C2 2020-01-01 -300
1 C1 2020-01-02 400
3 C3 2020-01-02 -100
2 C2 2020-01-03 -400
3 C3 2020-01-03 000

View on DB Fiddle

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    finally found the solution, though very convoluted! Any other optimized solutions ?

    WITH debits AS (
    SELECT d1.dr as id, d1.date, coalesce(d1.debit, 0) as debit FROM (
        SELECT dr, date,
            amt as debit
        FROM transactions
        GROUP BY dr, date, amt
    ) as d1
    GROUP BY d1.dr, d1.date, d1.debit
),
credits as (
    SELECT c1.cr as id, c1.date, coalesce(c1.credit, 0) as credit FROM (
        SELECT cr, date,
            amt as credit
        FROM transactions
        GROUP BY cr, date, amt
    ) as c1
    GROUP BY c1.cr, c1.date, c1.credit
),
ledger as (
    SELECT id, date, debit, 0 AS credit FROM debits
    UNION ALL
    SELECT id, date, 0 AS debit, credit FROM credits
    ORDER BY date, id
)
SELECT id, date,
       sum( coalesce(SUM(debit), 0) - coalesce(SUM(credit), 0) ) over (partition by id order by date) as balance
FROM ledger
GROUP BY date, id
ORDER by date, id, balance

    View on DB Fiddle


  2. 0

    Union the dr’s and crs

    SELECT 
  acc.id, 
  acc.name, 
  SUM(tx.amt) as balance 
FROM 
  accounts acc 
  JOIN transactions tx ON tx.dr = acc.id
GROUP BY   acc.id,   acc.name
UNION  ALL
SELECT 
  acc.id, 
  acc.name, 
  SUM(tx.amt * -1) as balance 
FROM 
  accounts acc 
  JOIN transactions tx ON tx.CR = acc.id
GROUP BY   acc.id,   acc.name;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search