Solution for SUM() in SQL query returning NULL instead zero (Differen between ISNULL and COALESCE)

When I was working on SQL queries I face this problem and then I have to shake my head to remember why this happen and what I did to solve it. So now I am documenting it here in more detail so that anyone who need help can get information here. Even I can check back when I will do same mistake 😉

Normally what happens is we use COUNT in QSL query and it return 0 if there is resolve fr that query. For example here is my table

id  |  name  |  Gender  |  Marks
1   |  Akash              |  M          |  20
2   |  Ashok              |  M          |  25
3   |  Manish            |  M          |  10
4   |  Mohit              |  M          |  30
5   |  Sumit              |  M          |  22
6   |  Vineet              |  M          |  11

Now I am going to fire query
SELECT COUNT(*) FROM Students WHERE Gender=’F’

This will return zero (0) because there is no female student so none of the record have F in Gender column. But if we fire similar query for getting SUM of marks like below
SELECT SUM(*) FROM Students WHERE Gender=’F’

This will not return zero (0), infect it will return a NULL because there is no record where Gender is F so SUM will give is NULL value unlike COUNT.

On many cases this NULL value will create programmatic errors we you have not checked for null before using its value. To avoid this case you can use two methods.

1. Using ISNULL (SELECT ISNULL(SUM(*),0) FROM Students WHERE Gender=’F’)
ISNULL just checks the value provided and if found it null then returns the second parameter passed to it. Which is quite simple so do the task.

2. Using COALESCE (SELECT COALESC(SUM(*),0) FROM Students WHERE Gender=’F’)
COALESCE is similar in terms of syntax (specially for this case) but what is actually does is, it returns first non null value passed in its parameters list.

Actually COALCASE can have any number of parameters like CAOLESCE(param1, param2, param3, 0). So here any non null value found among parameter sequence will be returned.

So both the methods above will return same value and your purpose will be solved. But in current case using ISNULL looks to be more efficient because it has to check only one condition while COALESCE is internally converted in SQL CASE statements to evaluate the result from input parameters list (this was till SQL Server 2008).

After all version after SQL Server 2008 COALESCE and ISNULL have same performance for this case because COALESCE internally started using same method like ISNULL when it has only two arguments

I hope this would help everyone to solve there problem and have an detailed insight for the solutions.

Leave a Reply