CODEWITHSUNDEEP
sqldatabaseoracle-database
antonpug
antonpug

Reputation: 14286

SQL query to add values of two columns containing null values?

Given table:

    ID   ONE   TWO
    X1   15    15
    X2   10    -
    X3   -     20

This query:

SELECT (ONE + TWO) FROM (TABLE)

Just returns the sum of X1's values but not the others since at least one column has a null value. How can I still add them even if there is a null? i.e. consider the null as a 0 maybe?

Upvotes: 7

Views: 21263

Answers (2)

Diego
Diego

Reputation: 36126

there is already a good answer, but I think it is worth mention to the antonpug (in case he doesn't know) that the reason why this is happening is that NULL is not a value that can be compared or summed.

NULL isn't 0 or '' (empty string), so every operation involving NULL will result NULL (10 + NULL = NULL), even (NULL=NULL) will evaluate to FALSE

Upvotes: 1

Mosty Mostacho
Mosty Mostacho

Reputation: 43434

SELECT (COALESCE(ONE, 0) + COALESCE(TWO, 0)) FROM (TABLE)

COALESCE will return the first non-null value found in the parameters from left to right. So, when the first field is null, it will take the 0.

That way, X2 will result in 10 + 0 = 10

Upvotes: 10

Related Questions