CODEWITHSUNDEEP
phpmysql
agung suprayitno
agung suprayitno

Reputation: 3

MYSQL SUM, IF and result to be 0

i have problem with 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 i have mysql syntax like 

SELECT trx, SUM(jmlgf) AS jumlahgf
      FROM gf_start
      GROUP BY trx
      HAVING jumlahgf < 4000000 ORDER BY 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 to get result like**

GNT3429 100000
GNT9851 500000
GNT1405 1000000
GNT9660 0
GNT7222 0
GNT2407 0
GNT3383 0

if SUM(jmlgf) >= 1600000

what should i to do with mysql syntax ?

i using PHP MYSQL

Upvotes: 0

Views: 83

Answers (1)

Kickstart
Kickstart

Reputation: 21533

Either use a variable, something like this:-

SELECT trx, @sum_so_far:=@sum_so_far + jumlahgf, IF(@sum_so_far >= 1600000, 0, jumlahgf) AS jumlahgf
FROM
(
    SELECT trx, SUM(jmlgf) AS jumlahgf
    FROM gf_start
    GROUP BY trx
    HAVING jumlahgf < 4000000 
    ORDER BY tgl ASC 
) sub0
CROSS JOIN
(
    SELECT @sum_so_far:=0
) sub1
LIMIT 40

Or join the results with another query to get the sum so far, something like this:-

SELECT trx, IF(rolling_cnt >= 1600000, 0, SUM(jmlgf)) AS jumlahgf
FROM gf_start
(
    SELECT a.trx, SUM(b.jmlgf) AS rolling_cnt
    FROM gf_start a
    INNER JOIN gf_start b
    ON a.trx = b.trx
    AND a.tgl <= b.tgl
    GROUP BY a.trx
) sub0
ON gf_start.trx = sub0.trx
GROUP BY trx
HAVING jumlahgf < 4000000 
ORDER BY tgl ASC 
LIMIT 40

Upvotes: 1

Related Questions