php - MYSQL SUM, IF and result to be 0 -

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