How can I set 0 if a query return a null value in MySql?

Question

I am not so into database and I have the following problem.

I have a query like this:

SELECT sum(intervento.IMP_IND_POS_AFF_MIN) 
FROM TID023_INTERVENTO intervento
INNER JOIN TID018_ENTEBENEFICIARIO enteBeneficiario 
   ON(enteBeneficiario.COD_ENT = intervento.COD_ENT)
INNER JOIN anagrafiche.TPG1029_PROVNUOIST provNuovIst 
   ON (provNuovIst.COD_PRV_NIS = enteBeneficiario.COD_PRV_NIS)
WHERE intervento.COD_TIP_BAN=1 AND intervento.IMP_IND_POS_AFF_MIN is not null;

This query works fine but, as you can see, it doesn't retrieve a set of rows but return a number that is obtained by the sum() function.

This number in some case could be null, in this case I don't want that this query return null (because it creates problem in my application) but in this case have to be returned the numeric value 0.

I know that MySql provide an ifnull() function to do something like this, here the reference:

http://www.w3schools.com/sql/sql_isnull.asp

but in this exalmpe it is used on single field on a table. How can I do something like this on my query output?

Solution 1

Use:

SELECT IFNULL(SUM(intervento.IMP_IND_POS_AFF_MIN), 0)

This means IFNULL can be applied to the value returned by SUM in the same way it is applied to a table field.

Solution 2

You can still use IFFNULL():

SELECT IFNULL(SUM(...), 0 ) FROM ... 

See also: Return 0 if field is null in MySQL

Comments

[GabrielVa]

Try the Case Statement to replace the Null with a 0?

[HoneyBadger]

How is your problem different from the situation in the link?

[Tom H]

I believe that the problem is that the query might return no results at all (which is looking like NULL for the front end?) - not that it is returning a NULL value