0

I have a problem to sum of field that always show null, I already tried all of these MySQL query,

(SUM(IFNULL(topup_amount,0))) AS total,
SUM(topup_amount) AS total2,
COALESCE(SUM(topup_amount),110),
IFNULL(SUM(topup_amount), 0),
COALESCE(SUM(topup_amount), 0)

But always show null result, I need 0 (zero) result. Anyone know about this?

Ponnarasu
  • 635
  • 1
  • 11
  • 24
Bobby
  • 253
  • 1
  • 4
  • 20
  • Did you try `SUM(COALESCE(topup_amount,0)` (though I cannot see that this would make much difference - I suspect the error lies elsewhere) – Strawberry Oct 21 '16 at 10:04
  • I can not see something wrong at least for the statement `SUM(IFNULL(topup_amount,0))`. Have you checked how the result set looks like after you applied `ifnull` but without aggregation? – PhillipD Oct 21 '16 at 10:07
  • this is the query i'm using, SELECT (SUM(IFNULL(topup_amount,0))) AS total, SUM(topup_amount) AS total2, COALESCE(SUM(topup_amount),110), IFNULL(SUM(topup_amount), 0), COALESCE(SUM(topup_amount), 0) FROM ap_topup_request WHERE retailer_id = 34290 AND date(topup_time) >= ( DATE_ADD( CURDATE(), INTERVAL - WEEKDAY(CURDATE()) DAY ) - INTERVAL 1 WEEK ) AND date(topup_time) < DATE_ADD( CURDATE(), INTERVAL - WEEKDAY(CURDATE()) DAY ) GROUP BY WEEK (topup_time) ORDER BY WEEK (topup_time) DESC LIMIT 1 – Bobby Oct 21 '16 at 10:12
  • Are you sure that your `where` condition actually returns a non zero result set? E.g. `ifnull` return `null` when working on empty sets. – PhillipD Oct 21 '16 at 10:16
  • sure, i think i've found the solution, it because the group by week syntax. After removed that one, finally i get the ifnull value. – Bobby Oct 21 '16 at 10:41

1 Answers1

0

After check all my query the problem is GROUP BY WEEK (topup_time). I removed this one, and finally i get the ifnull value. I don't know why it can be happen.

Bobby
  • 253
  • 1
  • 4
  • 20