php - MYSQL SUM, IF and result to be 0 -
i have problem mysql syntax, i have table like
gnt6988 rawabuaya 30000000 gnt3429 purwacaraka 100000 gnt1326 clara 15000000 gnt9059 bensugih1 6100000 gnt9620 bensugih2 6100000 gnt9851 abdulfattah 500000 gnt3927 sukses01 10000000 gnt4469 sukses02 10000000
and have mysql syntax
select trx, sum(jmlgf) jumlahgf gf_start grouping trx having jumlahgf < 4000000 order tgl asc limit 40
i got result
gnt3429 100000 gnt9851 500000 gnt1405 1000000 gnt9660 100000 gnt7222 100000 gnt2407 2100000 gnt3383 100000 gnt5586 100000 gnt1419 100000 **i want result like** gnt3429 100000 gnt9851 500000 gnt1405 1000000 gnt9660 0 gnt7222 0 gnt2407 0 gnt3383 0
if sum(jmlgf) >= 1600000
what should mysql syntax ?
i using php mysql
either utilize variable, this:-
select trx, @sum_so_far:=@sum_so_far + jumlahgf, if(@sum_so_far >= 1600000, 0, jumlahgf) jumlahgf ( select trx, sum(jmlgf) jumlahgf gf_start grouping trx having jumlahgf < 4000000 order tgl asc ) sub0 cross bring together ( select @sum_so_far:=0 ) sub1 limit 40
or bring together results query sum far, this:-
select trx, if(rolling_cnt >= 1600000, 0, sum(jmlgf)) jumlahgf gf_start ( select a.trx, sum(b.jmlgf) rolling_cnt gf_start inner bring together gf_start b on a.trx = b.trx , a.tgl <= b.tgl grouping a.trx ) sub0 on gf_start.trx = sub0.trx grouping trx having jumlahgf < 4000000 order tgl asc limit 40
php mysql
No comments:
Post a Comment