[SOLVED] running balance

[amosse]

Hello to everybody

 

I trying to get running balance, this is the query:

 

set @bal1=0, @bal2=0;
SELECT Id, Debit, Credit, Net, @bal2 := @bal2 + net AS CumNet
FROM (
select
id,
fin_record_debit AS Debit,
fin_record_credit AS Credit,
@bal1 := fin_record_debit - fin_record_credit AS Net
from fin_records
) AS tmp;

 

Unfortunately I get CumNet NULL, both fin_record_debit and fin_record_credit don't have any NULL value (default value 0,00)

 

Someone can help me? Please.

 

Amos

[fenway]

Have you read this?>

[amosse]

Got it!

 

Both solutions are working:

 

Solution 1

select x1.id,x1.fin_record_date,x1.fin_record_desc,x1.fin_record_debit,x1.fin_record_credit,sum(x2.fin_record_bal1) as fin_record_bal2 from
(select id,fin_record_date,fin_record_desc,fin_record_debit,fin_record_credit,(fin_record_debit-fin_record_credit) as fin_record_bal1 from fin_records) as x1
inner join (select id,fin_record_date,fin_record_desc,fin_record_debit,fin_record_credit,(fin_record_debit-fin_record_credit) as fin_record_bal1 from fin_records) as x2
on x1.id >= x2.id group by x1.id order by x1.fin_record_date,x1.id;

 

Solution 2

SET @bal1=0,@bal2=0;
SELECT Id, Debit, Credit, Net, @bal2 := @bal2 + net AS CumNet
FROM (
  select
    id,
    fin_record_debit AS Debit,
    fin_record_credit AS Credit,
    @bal1 := fin_record_debit - fin_record_credit AS Net
    from fin_records
) AS tmp;

 

BTW: Solution 2 is not working with MySQL Query Browser, but is working with MySQL Command Line Client

 

Amos