Mysql sum of fields not working when either field is NULL

Question

Hi i have this code in php to update a balance field in $table5. Now my problem is that when one of the two fields i.e. add or sub is NULL then this does not return balance.

    $fetch_balance = mysql_query("SELECT (SUM (IFNULL(`add`,0))-(SUM (IFNULL(`sub`,0)))          AS `bal` FROM `".$table5."` ");

Please help.

Your code snippet has unbalanced parentheses in the query, so the lack of results is probably due to an error rather than `IFNULL`, which will definitely do what you're expecting it to do. You don't need all those parentheses, so just try `"SELECT SUM(IFNULL(add, 0)) - SUM(IFNULL(sub, 0)) FROM $table5"` for your query string assignment. — Ed Gibbs, Dec 19 '13 at 15:37
@EdGibbs, good catch on the imbalanced parentheses. Looks like another case of a developer failing to check for mysql_query() returning `false`. — Bill Karwin, Dec 19 '13 at 15:40
There's also the space between `SUM` and the first `(`, which doesn't work as expected in all MySQL servers. — Jeremy Smyth, Dec 19 '13 at 15:41
thank you all for your insights and efforts. @nanashi dri's answer works — user2649343, Dec 19 '13 at 16:13

score 4 · Accepted Answer · answered Dec 19 '13 at 15:47

I tried your query, and it worked just fine, after some slight tweak: http://sqlfiddle.com/#!2/a6220/4

The final query should look like this: SELECT (SUM(IFNULL(add,0)))-(SUM(IFNULL(sub,0))) AS bal FROM ".$table5."

You forgot to add a ), and also, you had spaces after SUM(). SUM is a function, and should therefore not have any spaces before the ().

Gordon Linoff · Answer 2 · 2013-12-19T15:46:50.990

Your query simplified to be syntactically correct:

SELECT SUM(IFNULL(`add`, 0) - SUM(IFNULL(`sub`, 0) AS `bal`
FROM `".$table5."`;

Assuming the table has rows, then this will return a single, non-NULL value for bal. If the table has no rows, then it will return NULL.

You probably intend this logic:

select coalesce(sum(`add`), 0) - coalesce(sum(`sub`), 0) as bal
from . . .;

This does the replacement only if the entire sum() is NULL. The sum() aggregation function treats NULL values as 0.

Note: I replaced the ifnull() function with the ANSI standard equivalent coalesce().

score 0 · Answer 3 · answered Dec 19 '13 at 15:38

create table foo (bar1 integer, bar2 integer);

insert into foo (bar1, bar2) values (null, 1);
insert into foo (bar1, bar2) values (1, null);

select sum(coalesce(bar1, 0) + coalesce(bar2, 0)) from foo;
>2

select sum(bar1 + bar2) from foo;
>null

EDIT hint: try something like this:

SELECT SUM ( IFNULL(`add`,0) - IFNULL(`sub`,0) )

Mysql sum of fields not working when either field is NULL

3 Answers3