0

I have table with the following schema and demo data-

id  date        particulars          debit  credit  
104 29-12-2020  Crediting            NULL   1000    
105 29-12-2020  Purchased Item:A     2000   NULL    
106 29-12-2020  Purchased Item:B     200    NULL    
107 29-12-2020  Purchased Item:C     1000   NULL    
108 29-12-2020  Credited             NULL   1200    
109 29-12-2020  Credited             NULL   500     
110 29-12-2020  Credited             NULL   2100    
111 29-12-2020  Credited             NULL   1300    
112 01-01-2021  Purchased Item:Q     200    NULL    
113 02-01-2021  Purchased Item:A     2300   NULL

I want to calcualte running balance along with filters like from this date to this and pagination included. So far by referring this Answer I found the satisfied results, only the problem lies with WHERE clause.

Query i have used =

SET @variable = 0;
SELECT id, date, particulars, debit, credit, @variable := @variable + (COALESCE(`credit`,0) - COALESCE(`debit`,0)) `balance` 
FROM billing_ledger  ORDER BY id asc 

when i use this query im not getting expected balance as it should be

SET @variable = 0;
SELECT id, date, particulars, debit, credit, @variable := @variable + (COALESCE(`credit`,0) - COALESCE(`debit`,0)) `balance` 
FROM billing_ledger where date >= '2021-01-01' ORDER BY id asc 

i want this second query to also consider the above rows but to give me results for the following dates with where clause and also with LIMIT

Here is the fiddle: FiddleLink

In fiddle, we can see that the second query is not considering the rows above, and thus the balance is not correct as it is in the 1st query.

1
  • Tag with the version of MySQL you are using.
    – user212533
    Feb 22, 2021 at 14:35

1 Answer 1

0

As you are using the user-defined variables to iterate and so calculating the balnace, you have to set the initial variable ti the right amount

SET @variable = 0;
SELECT
-SUM(debit)  + SUM(credit) INTO @variable
FROM billing_ledger where date < '2021-01-01';
SELECT id, date, particulars, debit, credit
, @variable := @variable + (COALESCE(`credit`,0) - COALESCE(`debit`,0)) `balance` 
FROM billing_ledger where date >= '2021-01-01' ORDER BY id asc ;
✓

✓

 id | date       | particulars           | debit | credit | balance
--: | :--------- | :-------------------- | ----: | -----: | ------:
112 | 2021-01-01 | Purchased Item: Eggs  |   200 |   null |    2700
113 | 2021-01-02 | Purchased Item: Onion |  2300 |   null |     400
3
  • thanks this worked perfectly
    – pse
    Feb 23, 2021 at 7:07
  • How do you make a pagination work with this query? The initial value of the @variable will always be the same at the beginning of each paginated set. Aug 3, 2021 at 4:40
  • in mysql You use LIMIT and OFFSET to add pagnation
    – nbk
    Aug 3, 2021 at 7:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.