Calculate running total for each person

Question

I need to show the running total for each person in my database but I could only get the running total for all of them, so these are my tables on the pic

I already have this query:

SELECT 
    id, 
    studno,
    if(type=0,amount,0)debit,
    if(type=1,amount,0)credit,
    if(type=0,@bal:=@bal+amount,@bal:=@bal-amount) runningTotal
FROM
(SELECT id, studno, amount, 0 type from tblPayables 
UNION ALL 
SELECT id, studno, amount, 1 type from tblPayments)s, (SELECT @bal:=0)b
ORDER BY studno, id, type;

but the thing is I can only come up to this result:

The highlighted number should be 50 since it's for a different studno

why credit 5k applied after debit 10k and before debit 50? what is you add new credit with id 3? how results should look like? — Iłya Bursov, Feb 12 '15 at 23:13
it was just one of the requirements, coz I need to show the debit and credit alternately — Bernadine Bacolod, Feb 12 '15 at 23:15
the thing is i need every studno to have their own variable @bal — Bernadine Bacolod, Feb 12 '15 at 23:16
we couldn't because #3 should have something to pay for first or debit — Bernadine Bacolod, Feb 12 '15 at 23:17
we can, stud2 payed 50 - this is id 2, then stud 1 payed 50 - this is id 3 — Iłya Bursov, Feb 12 '15 at 23:18
basically - interleaving will not work, as payment id could be smaller than payable id for the same student, so order of your rows is undefined, this is why there is no sense to speak about running total — Iłya Bursov, Feb 12 '15 at 23:20

Barranka · Accepted Answer · 2015-02-12T23:42:58.463

You must write your query in such a way that the variable is initialized every time an Id changes.

Let's say you can write a query or view with the following columns:

id | studno | debit | credit
---+--------+-------+-------

So, let's write the query:

select id, debit, credit
     , @bal := ( -- If the value of the column `studno` is the same as the
                 -- previous row, @bal is increased / decreased;
                 -- otherwise, @bal is reinitialized
         case 
             when @studno = studno then @bal + debit - credit
             else debit - credit
         end
     ) as balance
     @studno := a.studno as studno -- It's important to update @studno
                                   -- AFTER you update @bal
from 
    (
        select @bal := 0
             , @studno := 0 -- This variable will hold the previous
                            -- value of the `studno` column
    ) as init, -- You must initialize the variables
    ( -- The above mentioned query or view
        select ...
        from ...
    ) as a
order by a.studno, a.id -- It's REALLY important to sort the rows

thank you so much but how can I hide the @studno:=studno column? — Bernadine Bacolod, Feb 12 '15 at 23:38
@BernadineBacolod See my updated answer (I removed the first `studno`, and put it as the last column. It's important to update `@studno` *after* `@bal`, because otherwise the running total won't work — Barranka, Feb 12 '15 at 23:44
@BernadineBacolod [Check this question and it's answers](http://stackoverflow.com/questions/17664436/cumulative-sum-over-a-set-of-rows-in-mysql) — Barranka, Feb 13 '15 at 00:09

score 0 · Answer 2 · answered Feb 12 '15 at 23:44

as I wrote in comments - existing query does interleaving wrong, it will fail if payment id for corresponding payable id is smaller

here is query which does interleaving properly and calculate running total as @Barranka suggested:

select studno, amount, @bal := (
         case 
             when @studno = studno then @bal + amount
             else amount
         end
     ) as balance,
  @studno := studno
from
(
        select @bal := 0
             , @studno := 0
) as init,
(
select 0 as t, studno, amount, (select count(*) from tblPayables as b where a.id>b.id and a.studno=b.studno) as trN
from tblPayables as a
union all
select 1 as t, studno, -amount, (select count(*) from tblPayments as b where a.id>b.id and a.studno=b.studno) as trN
from tblPayments as a
) as q
order by studno, trN, t

http://sqlfiddle.com/#!2/9e0cf1/1

Calculate running total for each person

2 Answers2