Geek Answers Handbook

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

This table:

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

This request:

SELECT (ONE + TWO) FROM (TABLE)

It simply returns the sum of the values ​​of X1 , but not the rest, because at least one column has a zero value. How can I add them even if there is zero? those. count zero as 0, maybe?

+7
source share
2 answers
 SELECT (COALESCE(ONE, 0) + COALESCE(TWO, 0)) FROM (TABLE)

COALESCE will return the first nonzero value found in the parameters from left to right. So, when the first field has a zero value, it will take the value 0.

Thus, X2 will result in 10 + 0 = 10

+10
source

there is already a good answer, but I think it is worth mentioning antonpug (in case he does not know) that the reason this happens is because NULL is not a value that can be compared or summed.

NULL is not 0 or '' (empty string), so every operation with NULL will result in NULL (10 + NULL = NULL), even (NULL = NULL) will be evaluated as FALSE

+1
source

More articles:


All Articles