blocks|key|2962091|text|更新的|type|unstyled|depth|inlineStyleRanges|offset|length|style|BOLD|entityRanges|data|2962092|如果4列中有1、2、NULL、5，则输出为8。|2962093|但是，在许多情况下，如果不检查NULLS，则使用SUM()是不安全的。|2962094|如果给定子句没有匹配的内容，则可以接收null。如果你在另一个函数中使用这个求和值，这可能是一个值得关注的问题。|2962095|这里有更多详细信息：https://msdn.microsoft.com/en-GB/library/ms187810.aspx|2962096|也请看一下https://msdn.microsoft.com/en-IN/library/ms190349.aspx合并方法|2962097|PS:也可以查看这个post+-+My+Select+SUM+query+returns+null.+It+should+return+0|2962098|这里有3个不检查NULL的图像，它返回NULL，而不是0。|2962099|和与ISNULL检查|2962100|📷|atomic|2962101|​|2962102|和不带ISNULL检查|2962103|2962104|2962105|2962106|和合并|2962107|2962108|2962109|entityMap|0|LINK|mutability|MUTABLE|url|https://msdn.microsoft.com/en-GB/library/ms187810.aspx|1|https://msdn.microsoft.com/en-IN/library/ms190349.aspx|2|https://stackoverflow.com/questions/17048343/my-select-sum-query-returns-null-it-should-return-0|3|IMAGE|IMMUTABLE|imageUrl|https://developer.qcloudimg.com/http-save/yehe-900000/da654471a7a9f4e1f71996e138d97c61.png|imageAlt|4|https://developer.qcloudimg.com/http-save/yehe-900000/0a1e92f68e6a81b8ed4909a2b168004d.png|5|https://developer.qcloudimg.com/http-save/yehe-900000/4d5f8dcd7843603571aa2c2174a48846.png^0|0|3|0|0|0|0|A|1I|0|0|5|1I|1|0|H|1G|2|0|0|0|A|0|0|1|3|0|0|0|B|0|0|0|1|4|0|0|0|3|0|0|1|5|0|0^^$0|@$1|2|3|4|5|6|7|1V|8|@$9|1W|A|1X|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|1Y|8|@]|D|@]|E|$]]|$1|H|3|I|5|6|7|1Z|8|@]|D|@]|E|$]]|$1|J|3|K|5|6|7|20|8|@]|D|@]|E|$]]|$1|L|3|M|5|6|7|21|8|@]|D|@$9|22|A|23|1|24]]|E|$]]|$1|N|3|O|5|6|7|25|8|@]|D|@$9|26|A|27|1|28]]|E|$]]|$1|P|3|Q|5|6|7|29|8|@]|D|@$9|2A|A|2B|1|2C]]|E|$]]|$1|R|3|S|5|6|7|2D|8|@]|D|@]|E|$]]|$1|T|3|U|5|6|7|2E|8|@$9|2F|A|2G|B|C]]|D|@]|E|$]]|$1|V|3|W|5|X|7|2H|8|@]|D|@$9|2I|A|2J|1|2K]]|E|$]]|$1|Y|3|Z|5|6|7|2L|8|@]|D|@]|E|$]]|$1|10|3|11|5|6|7|2M|8|@$9|2N|A|2O|B|C]]|D|@]|E|$]]|$1|12|3|Z|5|6|7|2P|8|@]|D|@]|E|$]]|$1|13|3|W|5|X|7|2Q|8|@]|D|@$9|2R|A|2S|1|2T]]|E|$]]|$1|14|3|Z|5|6|7|2U|8|@]|D|@]|E|$]]|$1|15|3|16|5|6|7|2V|8|@$9|2W|A|2X|B|C]]|D|@]|E|$]]|$1|17|3|W|5|X|7|2Y|8|@]|D|@$9|2Z|A|30|1|31]]|E|$]]|$1|18|3|Z|5|6|7|32|8|@]|D|@]|E|$]]|$1|19|3|-4|5|6|7|33|8|@]|D|@]|E|$]]]|1A|$1B|$5|1C|1D|1E|E|$1F|1G]]|1H|$5|1C|1D|1E|E|$1F|1I]]|1J|$5|1C|1D|1E|E|$1F|1K]]|1L|$5|1M|1D|1N|E|$1O|1P|1Q|-4]]|1R|$5|1M|1D|1N|E|$1O|1S|1Q|-4]]|1T|$5|1M|1D|1N|E|$1O|1U|1Q|-4]]]]

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: <a href="https://msdn.microsoft.com/en-GB/library/ms187810.aspx" rel="noreferrer">https://msdn.microsoft.com/en-GB/library/ms187810.aspx</a>

Please also look at COALESCE method
<a href="https://msdn.microsoft.com/en-IN/library/ms190349.aspx" rel="noreferrer">https://msdn.microsoft.com/en-IN/library/ms190349.aspx</a>

PS: Also check out this post - <a href="https://stackoverflow.com/questions/17048343/my-select-sum-query-returns-null-it-should-return-0">My Select SUM query returns null. It should return 0</a>

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

SUM with ISNULL CHECK
<a href="https://i.stack.imgur.com/M2Sdi.png" rel="noreferrer"><img src="https://i.stack.imgur.com/M2Sdi.png" alt="enter image description here"></a>

SUM without ISNULL CHECK

<a href="https://i.stack.imgur.com/B1YIB.png" rel="noreferrer"><img src="https://i.stack.imgur.com/B1YIB.png" alt="enter image description here"></a>

SUM with COALESCE
<a href="https://i.stack.imgur.com/WQZTh.png" rel="noreferrer"><img src="https://i.stack.imgur.com/WQZTh.png" alt="enter image description here"></a>

blocks|key|2962120|text|在和聚合之前使用聚结方法更好。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2962121|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

Its better to use COALESCE method before the SUM aggregation.

blocks|key|3067346|text|是的，它很安全。可以在不处理空值的情况下使用Sum。你也可以检查一下。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3067347|你也可以这样用。|3067348|ISNULL(SUM(COL1),0)|3067349|返回表达式中所有值的和，或仅返回不同的值。和只能与数字列一起使用。忽略空值。|3067350|参考：https://msdn.microsoft.com/en-IN/library/ms187810.aspx|offset|length|3067351|entityMap|0|LINK|mutability|MUTABLE|url|https://msdn.microsoft.com/en-IN/library/ms187810.aspx^0|0|0|0|0|3|1I|0|0^^$0|@$1|2|3|4|5|6|7|T|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|U|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|V|8|@]|9|@]|A|$]]|$1|F|3|G|5|6|7|W|8|@]|9|@]|A|$]]|$1|H|3|I|5|6|7|X|8|@]|9|@$J|Y|K|Z|1|10]]|A|$]]|$1|L|3|-4|5|6|7|11|8|@]|9|@]|A|$]]]|M|$N|$5|O|P|Q|A|$R|S]]]]

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 : <a href="https://msdn.microsoft.com/en-IN/library/ms187810.aspx" rel="noreferrer">https://msdn.microsoft.com/en-IN/library/ms187810.aspx</a>

blocks|key|2206881|text|由于SUM总是忽略空值，所以在执行SUM时使用NULL和ignore并不是强制性的。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2206882|有关更多信息，请参考链接：https://msdn.microsoft.com/en-IN/library/ms187810.aspx。|offset|length|2206883|entityMap|0|LINK|mutability|MUTABLE|url|https://msdn.microsoft.com/en-IN/library/ms187810.aspx^0|0|D|1I|0|0^^$0|@$1|2|3|4|5|6|7|N|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|O|8|@]|9|@$D|P|E|Q|1|R]]|A|$]]|$1|F|3|-4|5|6|7|S|8|@]|9|@]|A|$]]]|G|$H|$5|I|J|K|A|$L|M]]]]

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

Refer the link : <a href="https://msdn.microsoft.com/en-IN/library/ms187810.aspx" rel="noreferrer">https://msdn.microsoft.com/en-IN/library/ms187810.aspx</a> for more info.

blocks|key|2206916|text|您的这两个查询都可以返回空值。|type|unstyled|depth|inlineStyleRanges|offset|length|style|BOLD|entityRanges|data|2206917|的确，Sum()将忽略Null值，只添加数字，但是当它有没有行来计算和，或者当所有值都为空时，它将返回Null。|2206918|所以，为了保证你不会得到零，你必须写：|2206919|IsNull(Sum(Col1),0)|code-block|syntax|javascript|2206920|观察实例：|2206921|​|2206922|📷|atomic|2206923|2206924|entityMap|0|IMAGE|mutability|IMMUTABLE|imageUrl|https://developer.qcloudimg.com/http-save/yehe-900000/b842909e2070cf0a5f3f1090f5f2e6de.png|imageAlt^0|7|3|0|S|3|13|6|0|0|0|0|0|0|1|0|0|0^^$0|@$1|2|3|4|5|6|7|15|8|@$9|16|A|17|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|18|8|@$9|19|A|1A|B|C]|$9|1B|A|1C|B|C]]|D|@]|E|$]]|$1|H|3|I|5|6|7|1D|8|@]|D|@]|E|$]]|$1|J|3|K|5|L|7|1E|8|@]|D|@]|E|$M|N]]|$1|O|3|P|5|6|7|1F|8|@]|D|@]|E|$]]|$1|Q|3|R|5|6|7|1G|8|@]|D|@]|E|$]]|$1|S|3|T|5|U|7|1H|8|@]|D|@$9|1I|A|1J|1|1K]]|E|$]]|$1|V|3|R|5|6|7|1L|8|@]|D|@]|E|$]]|$1|W|3|-4|5|6|7|1M|8|@]|D|@]|E|$]]]|X|$Y|$5|Z|10|11|E|$12|13|14|-4]]]]

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:
<pre><code>IsNull(Sum(Col1),0)
</code></pre>
Observe example:
<a href="https://i.stack.imgur.com/iRYmC.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/iRYmC.png" alt="Example showing that depending on your data IsNull might be required to guarantee that result is Not Null" /></a>

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

<pre><code>SUM(ISNULL(COL1,0))
</code></pre>

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

<pre><code>SUM(COL1)
</code></pre>

I did some small tests and I couldnt see results

Is it safe to use SUM() without ISNULL()

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

我正试图因此而获得SP的表现。我对SUM和ISNULL有一个疑问。当我总结一个专栏时，我应该使用ISNULL吗？如果没有ISNULL，那么使用SUM()安全吗？我的例子如下SUM(ISNULL(COL1,0))由于ISNULL成本很高，所以我打算使用以下不带ISNULL的SUMSUM(COL1)我做了一些小测试，看不见...

问在不使用ISNULL()的情况下使用SUM()安全吗？
EN

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问在不使用ISNULL()的情况下使用SUM()安全吗？EN