jlsalles jlsalles - 2 months ago 8
SQL Question

MYSQL avoiding null values for sum operations?

I need to make a collunm that counts the amount of variables witch are above 0, and that wont return null if there if there is any not null value in it, for each value in an id collunm

what i managed to make is using a sum between some boolean operations

IF 'A' THEN 'B' ELSE 'C' (at least thats what i got)

select ID, `jul`, `aug`, `set`, `oct`, `nov`, `dec`,
((((not `jul`) or 1) and (`jul` or 0))
+(((not `aug`) or 1) and (`aug` or 0))
+(((not `set`) or 1) and (`set` or 0))
+(((not `out`) or 1) and (`out` or 0))
+(((not `nov`) or 1) and (`nov` or 0))
+(((not `dec`) or 1) and (`dec` or 0))) as sum from table;


it works at first view, but if there is any null value in a line, the 'sum' returns null for each respective id.

What could i do to avoid this problem?

Answer

try

SUM( IFNULL(jul,0)+IFNULL(ago,2) ) as sum from table

/* 
   obs: the SUM is good to sum multiple values
   IFNULL returns 0 to the sum if jul is null and 2 for ago if ago is null in the example.
*/

i think it works. :)

Comments