SQL SUM to ignore NULL value

Question

I have a table TEST_TABLE as follows:

Name     x_col       y_col
=======================
Jay     NULL     2

This is a simplistic representation of a much larger issue but will suffice.

When I do the following query I get NULL returned

SELECT SUM(x_col + y_col) FROM TEST_TABLE WHERE Name='Jay'

I want it to be 2. I thought the SUM() method ignores NULL values. How can I ignore values that are null in this query? Or actually in general, as this is a problem for a lot of my algorithms.

@arsenal88 . . . I removed the inconsistent database tags. However, your question regards standard SQL so the database doesn't actually matter. — Gordon Linoff
– Gordon Linoff, Commented May 29, 2020 at 10:48

Gordon Linoff · Accepted Answer · 2020-05-29 10:47:10Z

4

You get NULL because NULL + 2 returns NULL. The SUM() has only one row, and if the + expression is NULL, then the SUM() returns NULL.

If you want NULL to be treated as 0, the use COALESCE():

SELECT SUM(COALESCE(x_col, 0) + COALESCE(y_col, 0))
FROM TEST_TABLE
WHERE Name = 'Jay';

One final note. If you start with your data and filtered out all rows, then the result will still be NULL. To get 0, you need an additional COALESCE():

SELECT COALESCE(SUM(COALESCE(x_col, 0) + COALESCE(y_col, 0)), 0)
FROM TEST_TABLE
WHERE Name = 'Jayden';

answered May 29, 2020 at 10:47

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

jarlh · Accepted Answer · 2020-05-29 10:47:06Z

0

Use COALESCE to replace NULL with 0.

SELECT sum(coalesce(x_col, 0) + coalesce(y_col, 0)) FROM TEST_TABLE WHERE Name='Jay'

answered May 29, 2020 at 10:47

jarlh

44.9k8 gold badges52 silver badges68 bronze badges

Collectives™ on Stack Overflow

SQL SUM to ignore NULL value

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related