34

I am trying to hence the performance of an SP. I have a doubt in my mind about SUM and ISNULL. When I sum up a column, should I use ISNULL? Is it SAFE to use SUM() without ISNULL. My example is below

SUM(ISNULL(COL1,0))

Since ISNULL costs a lot, I intend to use SUM without ISNULL like below

SUM(COL1)

I did some small tests and I couldnt see results

Arif YILMAZ
  • 5,754
  • 26
  • 104
  • 189
  • 3
    Check my post about `NULL vs 0`: http://blog.devart.com/what-is-faster-inside-stream-aggregate-hash-match.html – Devart May 18 '16 at 09:24

5 Answers5

31

Yes its safe . You can use Sum without handling NULL Value. You can also check that.

You can use like that also.

ISNULL(SUM(COL1),0).

Returns the sum of all the values, or only the DISTINCT values, in the expression. SUM can be used with numeric columns only. Null values are ignored.

For Reference : https://msdn.microsoft.com/en-IN/library/ms187810.aspx

Sandeep Kumar
  • 1,172
  • 1
  • 9
  • 22
  • 2
    As mentioned above in my answer. You can't. Refer - http://stackoverflow.com/questions/1088648/sql-sum-3-columns-when-one-column-has-a-null-value – vohrahul May 18 '16 at 09:58
  • In this question their are 3 different columns . After using Sum function he is adding some more columns. In this question they suggest that use ISNULL for column not for SUM... – Sandeep Kumar May 18 '16 at 10:05
  • I have checked that You can read this from MSDN https://msdn.microsoft.com/en-IN/library/ms187810.aspx – Sandeep Kumar May 18 '16 at 10:21
  • I have updated my answer to better explain what I really mean here. Maybe the answer is correct for @ayilmaz, but if we don't check for NULL, it maybe a point of concern in many cases. However, your answer is correct in this situation. +1 – vohrahul May 18 '16 at 11:45
  • In views on SQL server 2005, SUM(col1) will return NULL if any col1 value is NULL, and if you put ISNULL on the sum, it will just turn this NULL into 0, which is incorrect. This is a bad habit to form. – Henrik Erlandsson Mar 21 '19 at 12:27
10

Updated

If you have [1, 2, NULL, 5] in 4 columns, it will give the output as 8.

However, it is not safe to use SUM() without checking for NULLS in many cases.

You can receive null when it has no matching content for a given clause. And if you are using this SUMMED value in another function, that maybe a point of concern.

More details here: https://msdn.microsoft.com/en-GB/library/ms187810.aspx

Please also look at COALESCE method https://msdn.microsoft.com/en-IN/library/ms190349.aspx

PS: Also check out this post - My Select SUM query returns null. It should return 0

Here are 3 images that shows without checking for NULL it returns NULL and not 0.

SUM with ISNULL CHECK enter image description here

SUM without ISNULL CHECK

enter image description here

SUM with COALESCE enter image description here

Community
  • 1
  • 1
vohrahul
  • 1,123
  • 10
  • 17
  • Sum can be used without handling NULL values @vohrahul – Sandeep Kumar May 18 '16 at 09:51
  • @SandeepKumar - Please refer this post: http://stackoverflow.com/questions/1088648/sql-sum-3-columns-when-one-column-has-a-null-value You need to check for ISNULL. Or you receive null – vohrahul May 18 '16 at 09:57
  • Nulls on the columns being aggregated are ignored by sum (), but at conclusion you might require zero eg. coalesce (sum (column1)). Placing coalesce or isnull inside the sum is wasteful. – Paul Maxwell May 18 '16 at 09:59
  • In my answer, I did not mention that he needs to use ISNULL always. I suggested using coalesce to get 0 as a minimum value in order to execute SUM(). – vohrahul May 18 '16 at 10:02
  • http://stackoverflow.com/questions/1088648/sql-sum-3-columns-when-one-column-has-a-null-value in this question they suggest for use ISNULL with column not with SUM. – Sandeep Kumar May 18 '16 at 10:06
  • @SandeepKumar - Please check my updated answer with attached screenshots. Without checking for null it returns NULL and NOT 0. Hence, it is not safe since in a stored procedure you might want to perform other actions. And performing actions on NULL is NOT SAFE. – vohrahul May 18 '16 at 10:19
  • You can use ISNULL(Sum([Repo]),0) ... Use this https://msdn.microsoft.com/en-IN/library/ms187810.aspx and read MSDN about Sum Returns the sum of all the values, or only the DISTINCT values, in the expression. SUM can be used with numeric columns only. Null values are ignored. – Sandeep Kumar May 18 '16 at 10:21
  • In a way you are correct. It ignores NULL. However, it maybe a point of concern if this value is used somewhere else. Please check my updated answer. – vohrahul May 18 '16 at 11:44
  • 6
    Worth mentioning the slightly deceptive case `SELECT SUM(col1) + SUM(col2)` - it will return `null` if `col1` contains only nulls, even if `col2` contains values, as the `+` operator returns `null` if any of its operands are `null` (as that's what the the ANSI standard requires, I think). This answer should get more up-votes! – Chris Jun 08 '18 at 10:49
  • 1
    Thanks @Chris. That's why i clearly mentioned in my comments as well. Not using null check might be a concern if you are using this value in some other expression or calculation. Anyway, thanks for the appreciation. – vohrahul Jun 09 '18 at 20:20
  • 1
    Sorry for adding to an old thread, but sometimes, depending on the context, SUM of NULL should return NULL where 0 and NULL could mean very different things. – The Lyrist Apr 11 '19 at 02:33
6

Its not mandatory to use NULL and COALESCE while doing SUM because SUM always ignore null values.

Refer the link : https://msdn.microsoft.com/en-IN/library/ms187810.aspx for more info.

Ragesh
  • 740
  • 3
  • 9
  • 5
    Unless **all** values are null, in which case it returns null, as pointed out by @vohrahul. – Chris Jun 08 '18 at 10:47
3

Its better to use COALESCE method before the SUM aggregation.

Uday Prasanna N
  • 50
  • 1
  • 7
  • 1
    Better to use *after*, ie, `COALESCE(SUM(col, 0))`, as pointed out by @Used_By_Already above. – Chris Jun 08 '18 at 10:50
1

Both of your queries CAN return Null value.

It is true that Sum() will ignore Null values and add only numbers, but when it has NO ROWS to calculate sum from or when ALL VALUES ARE NULL it will return Null.

So, to guarantee you won't get Null you have to write:

IsNull(Sum(Col1),0)

Observe example:

Example showing that depending on your data IsNull might be required to guarantee that result is Not Null

ivo.tisljar
  • 177
  • 10