Adding columns in MySQL - how to treat NULL as 0

Question

I have simple table (test) where I need to perform some additions (I want to get a row total and a column total).

 id  var1  var2
 1   NULL  NULL
 2   10    NULL

For column totals, summing works as expected (NULL is ignored in the addition):

SELECT SUM(var1) FROM test

10

For row totals, addition does not ignore NULL (if any column is NULL the result is NULL):

SELECT var1+var2 FROM test

NULL
NULL

What I want it to return is:

SELECT var1+var2 FROM test

NULL
10

Is there a way to get MySQL to treat NULL as 0 in an addition?

Answer 1

You want to use coalesce():

select coalesce(var1, 0) + coalesce(var2, 0)

coalesce() is ANSI standard and available in most databases (including MySQL).

Answer 2

use the IFNULL function

SELECT IFNULL(var1, 0) + IFNULL(var2, 0) FROM test

Answer 3

select 
Case when (coalesce(`var1`, 0) + coalesce(`var2`, 0))=0
then NULL
else coalesce(`var1`, 0) + coalesce(`var2`, 0)
end
from test

SQL FIDDLE