CODEWITHSUNDEEP
sqlsql-serverdatabase
BlackCat
BlackCat

Reputation: 2044

Why Sum in database query giving NULL

Suppose I have a table named "Expense" and it has some month-wise expense which may contain some "NULL", now I want to check the yearly expenses in total with this below query:-

enter image description here

Select Sum(January) + Sum (February) ..... (I skipped the rest 10 months)
from Expense

This gives result as "NULL"

How can I avoid this situation? I think there are more convenient way to check the yearly sum

Upvotes: 1

Views: 2190

Answers (7)

Salman Arshad
Salman Arshad

Reputation: 272096

All arithmetic or logical operations involving NULL yield NULL. For example:

SELECT 1 + NULL -- NULL

You must convert NULL to zeros before you can + them:

SELECT
    COALESCE(SUM(January), 0) +
    COALESCE(SUM(February) , 0) +
    ...

It is also possible to add the columns first and then calculate the sum:

SELECT SUM(
    COALESCE(January, 0) +
    COALESCE(February, 0) +
)

Be advised that (i) SUM skips NULL values (ii) returns NULL instead of 0 if all values are NULL:

SELECT SUM(a) FROM (VALUES
    (1),
    (2),
    (NULL)
) AS v(a) -- returns 3 instead of NULL

It will return NULL if all values encountered were NULL:

SELECT SUM(a) FROM (VALUES
    (CAST(NULL AS INT)),
    (NULL),
    (NULL)
) AS v(a) -- returns NULL instead of 0

Upvotes: 4

Pawel Czapski
Pawel Czapski

Reputation: 1864

Because null + value is always null and in your sample some months sums are null, you can avoid this by adding ISNULL

Select isnull(Sum(January),0) + 
    isnull(Sum(February),0) 
--..... (I skipped the rest 10 months)
from Expense

Alternatively you can use below way:

Select Sum(
    isnull(January,0) + 
    isnull(February,0) 
    )
--..... (I skipped the rest 10 months)
from Expense

Upvotes: 0

Barbaros Özhan
Barbaros Özhan

Reputation: 65218

Just use coalesce [ with 0 as the second argument ] to replace nulls for all month columns, otherwise you can not get true results from aggregation of numeric values :

select sum(coalesce(January,0)+coalesce(February,0) ... )
  from Expense

Upvotes: 1

Apep
Apep

Reputation: 111

You can use ISNULL(SUM(January),0).

Upvotes: 0

Canburak Tümer
Canburak Tümer

Reputation: 1063

Any arithmetic function will return null if there is at least one null value in the given column. That's why you should use functions like coalesce or isNull (MSSQL), NVL (Oracle).

Upvotes: 0

Ilyes
Ilyes

Reputation: 14928

That because you have NULL values, you can use Case, Coalesce or IIF:

Select SUM(IIF(Col IS NULL, 0, Col))

Select SUM(CASE WHEN Col IS NULL THEN 0 ELSE Col END)

Select COALESCE(Sum(Col), 0)

Upvotes: 0

Fahmi
Fahmi

Reputation: 37473

use coalesce function to convert null to 0 then use sum

Select Sum(coalesce(January,0)) + Sum (coalesce(February,0)) ..... (I skipped the rest 10 months)
from Expense

Upvotes: 2

Related Questions