Summing up columns in MySQL

Question

I have an installment table as shown below and I would like to get the balance amount based on the amount paid by the client. For e.g. for the first installment it was fully paid, second installment he still owes $10 but will be covered first by the third payment and hence the balance amount will be $30 on the third payment. How can I go about doing this in MySQL?

installment table:

acc_name	installment_amt	installment_no	paid_amt
A	100	1	100
A	100	2	90
A	100	3	80
A	100	4
A	100	5

resulting_table:

acc_name	installment_amt	installment_no	paid_amt	balance
A	100	1	100	0
A	200	2	190	0
A	300	3	270	30
A	400	4
A	500	5

Another scenario would be the third payment is unable to cover fully both the 2nd and third installment as such

installment table:

acc_name	installment_amt	installment_no	paid_amt
A	100	1	100
A	100	2	90
A	100	3	5
A	100	4
A	100	5

resulting_table:

acc_name	installment_amt	installment_no	paid_amt	balance
A	100	1	100	0
A	200	2	190	5
A	300	3	195	100
A	400	4
A	500	5

What is the reason to do this type of calculation? What is the logic here? — FanoFN, Mar 08 '22 at 04:40
1) What version of MySQL? 2) What does "resulting_table" represent: your expected output? 3) If yes, where does the "balance" information come from? It's not included any of the other tables above. 4) What have you tried, and what was the result? — SOS, Mar 08 '22 at 04:46
@SOS Version is 8.0.37 Yes the resulting table is my expected output. "balance" comes from the installment amount - paid amount for e.g. at installment 3, the total installment amount is 300 while paid amount is 270(195 for scenario 2), hence 300 - 270. However, as you can see, installment 2 was short of $10 and thus any amount paid in installment 3 will first be used to cover the shortfall of this $10 and the remaining will be used to pay for installment 3. — Danny Han, Mar 08 '22 at 05:08
Sounds like you're looking for a running total. https://stackoverflow.com/questions/664700/calculate-a-running-total-in-mysql — SOS, Mar 08 '22 at 05:11
@FanoFN the reason is for customer repayment behaviour. The logic is actually to come up with the days after due date(not shown here) of the installment. — Danny Han, Mar 08 '22 at 05:12
@SOS yes a running total with an exception because you can see installment 2 where 200 - 190 = 10 but this 10 would be covered for by the third installment and hence the balance would be 0 — Danny Han, Mar 08 '22 at 05:14
TBH, I don't think you can do all that with a simple query in MySQL, even *with* window functions. There's no guarantee how far back you'd have to apply money from a current payment, It could be 1, 2, 3 or N. Window functions don't support that kind of dynamic evaluation. So this would likely require some sort of iteration, which is not great from a performance standpoint. Also, even if you could do it without involving iteration, the "results" are less than ideal from an audit perspective. It'd be difficult to track what payments were applied where and why. — SOS, Mar 08 '22 at 05:46

score 0 · Answer 1 · answered Mar 09 '22 at 01:38

I agree that this is not an easy thing to do in MySQL. If your aiming to output the result into the web app, it's probably much easier to obtain the necessary information from MySQL and figure out how to do it on a web app. That being said, I did actually manage to get the result using a combination of LAG(), LEAD() and a quite complicated CASE expression:


SELECT acc_name,
       installment_amt,
       installment_no,
       paid_amt,
       CASE WHEN paid_amt > 0
            AND paid_amt+nxp >= installment_amt
            AND diff > 0
        THEN 0
            WHEN paid_amt > 0
            AND paid_amt+nxp < installment_amt
            AND nxp > 0
        THEN nxp
            WHEN paid_amt > 0
            AND paid_amt+nxp < installment_amt
            AND LAG(diff) OVER (PARTITION BY acc_name ORDER BY installment_no) > 0
        THEN LAG(installment_amt-diff) OVER (PARTITION BY acc_name ORDER BY installment_no)
            WHEN paid_amt > 0
            AND paid_amt+nxp < installment_amt
            AND diff <0
        THEN ABS(diff)+paid_amt
        ELSE 0
       END balance
  FROM
(SELECT *,
       CASE WHEN paid_amt > 0
             AND paid_amt+nxp >= installment_amt
            THEN paid_amt+nxp-installment_amt 
            WHEN paid_amt > 0
             AND paid_amt+nxp < installment_amt
            THEN (paid_amt+nxp)-installment_amt
          END AS diff
FROM
(SELECT *,
      CASE WHEN paid_amt < installment_amt THEN
                LEAD(paid_amt) OVER (PARTITION BY acc_name ORDER BY installment_no)
           ELSE 0 END nxp
FROM installment) V
) R

The first step is to get the next paid_amt using LEAD() with a CASE expression. Then make that as a subquery. The next step is to use that LEAD() result from the subquery to do some simple calculation but with a slightly more advanced CASE conditions. Once that done, turn it into a subquery. The last step have a much more complicated CASE expression that I use with LAG(). By this point, I already managed generate two columns; nxp - is the next paid_amt that was obtained in the first subquery and diff - is the difference calculated in the second subquery.

I don't know how well this query will work if there's other variation of data than the one you've mentioned but at the moment, it does return the desired result.

Here's a demo fiddle

Summing up columns in MySQL

1 Answers1