Mysql sum of fields not working when either field is NULL

php mysql select sum ifnull
user2649343 picture user2649343 · Dec 19, 2013 · Viewed 8.6k times · Source

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.

Answer

ZeroZipZilch picture ZeroZipZilch · Dec 19, 2013

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 ().

Related questions

How can I properly use a PDO object for a parameterized SELECT query

I've tried following the PHP.net instructions for doing SELECT queries but I am not sure the best way to go about doing this. I would like to use a parameterized SELECT query, if possible, to return the ID in …

Read More
How to create virtual column using MySQL SELECT?

If I do SELECT a AS b and b is not a column in the table, would query create the "virtual" column? in fact, I need to incorporate some virtual column into the query and process some information into the …

Read More
Selecting all columns that start with XXX using a wildcard?

I have several columns in my databases with similar names. How do I select those based on the word they start with? Here's an example table layout: I tried selecting all info for a particular thing (food kind in this …

Read More