Search code examples
mysqlsqldatabasecountrdbms

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

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

  • 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.