CODEWITHSUNDEEP
sqlsql-servernulladdition
user776676
user776676

Reputation: 4385

How to force addition when a value is missing in SQL?

I want to do an addition in a select statement like this:

select (I + j + k) as total from MyTable ...

As expected, if any of the I, j, k is null, total is returned as null.

How do I code this select so that when any of the I, j, k is null (missing), the missing value is considered 0 for the purpose of addition (so that total is never null)? Thanks.

Upvotes: 0

Views: 89

Answers (2)

Hogan
Hogan

Reputation: 70523

select ISNULL(I,0) + ISNULL(j,0) + ISNULL(k,0) as total from MyTable ...

or

select IFNULL(I,0) + IFNULL(j,0) + IFNULL(k,0) as total from MyTable ...

on mysql

Upvotes: 3

anon
anon

Reputation: 

SELECT total = COALESCE(l,0) + COALESCE(j,0) + COALESCE(k,0)
FROM dbo.MyTable;

Upvotes: 5

Related Questions