CODEWITHSUNDEEP
mysql
Black_Fogg
Black_Fogg

Reputation: 38

Issue in calculating balance from debit and credit in MySQL

I am using MySQL v5.7 I need to calculate running balance form debit and credit columns. the mention query works perfectly but when i restrict the rows in where condition it shows wrong values. actually it calculates the sum for all rows and then show the results of required rows but in that case the required balance goes worng. here is my query that works when i fetch all rows.

SELECT
    vch.TR_ID,
  vch.TR_CUST_ID,
    vch.TR_DEBIT Debit,
    vch.TR_CREDIT Credit,
    COALESCE ((SELECT SUM(TR_CREDIT) - SUM(TR_DEBIT) FROM  all_trans vch2 WHERE vch2.TR_ID <= vch.TR_ID ),0 ) AS Balance,
    vch.TR_DATETIME DateTime
FROM    `falcondb`.`all_trans` vch
WHERE
vch.TR_DATETIME BETWEEN 
STR_TO_DATE('2020-07-24 11:19:18','%Y-%m-%d %H:%i:%s')AND 
STR_TO_DATE('2020-10-24 11:19:18','%Y-%m-%d %H:%i:%s')
ORDER BY vch.TR_ID;

This is transaction table having CUST_ID and my primary key is TR_ID I have some other attached tables which will be joined with this but here they doesn't matter. that's why I didn't show here in order to make it simple for you guys. Screen shots of both conditions attached. query with all rows query for specific cust_id

What my sub query actually do is: it calculate it for all entries in the table and shows me only with cust_id=1. that's the actual problem. it must calculate the balance for the selected rows only. it actually calculates for all rows and show me the rows from that result. the below screenshot explains it

This is actually my query do when I put condition for Cust_id

Upvotes: 0

Views: 710

Answers (1)

GMB
GMB

Reputation: 222432

If you want a per-TR_CUST_ID balance, then you need to include a correlation clause on that column in the subquery:

SELECT
    vch.TR_ID,
    vch.TR_CUST_ID,
    vch.TR_DEBIT Debit,
    vch.TR_CREDIT Credit,
    COALESCE (
        (
            SELECT SUM(TR_CREDIT) - SUM(TR_DEBIT) 
            FROM all_trans vch2 
            WHERE vch2.TR_ID <= vch.TR_ID AND vch2.TR_CUST_ID = vch.TR_CUST_ID
        ),                                  --^-- here
        0 
    ) AS Balance,
    vch.TR_DATETIME DateTime
FROM all_trans vch
WHERE 
    vch.TR_CUST_ID = 1
    AND vch.TR_DATETIME BETWEEN '2020-07-24 11:19:18' AND '2020-10-24 11:19:18'
ORDER BY vch.TR_ID;

Note that I removed the STR_TO_DATE() expressions: they are not needed, since the values that you have are perfectly valid literal dates in MySQL already.

Upvotes: 1

Related Questions