blocks|key|1296360|text|如果该列的值为0，则没有问题，我的猜测是null值有问题，在这种情况下，您将需要使用IsNull(Column,+0)来确保它始终至少为0。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|1296361|entityMap^0|16|H|0^^$0|@$1|2|3|4|5|6|7|H|8|@$9|I|A|J|B|C]]|D|@]|E|$]]|$1|F|3|-4|5|6|7|K|8|@]|D|@]|E|$]]]|G|$]]

If the column has a 0 value, you are fine, my guess is that you have a problem with a Null value, in that case you would need to use <code>IsNull(Column, 0)</code> to ensure it is always 0 at minimum.

blocks|key|1511004|text|您可以使用ISNULL|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|1511005|ISNULL(field,+VALUEINCASEOFNULL)|code-block|syntax|javascript|1511006|entityMap^0|5|6|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@$9|N|A|O|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|P|8|@]|D|@]|E|$I|J]]|$1|K|3|-4|5|6|7|Q|8|@]|D|@]|E|$]]]|L|$]]

You can use <code>ISNULL</code>:

<pre><code>ISNULL(field, VALUEINCASEOFNULL)
</code></pre>

blocks|key|1511018|text|SELECT+sum(isnull(TotalHoursM,0))+
+++++++++%2B+isnull(TotalHoursT,0)+
+++++++++%2B+isnull(TotalHoursW,0)+
+++++++++%2B+isnull(TotalHoursTH,0)+
+++++++++%2B+isnull(TotalHoursF,0))
AS+TOTAL+FROM+LeaveRequest|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|1511019|unstyled|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|G|8|@]|9|@]|A|$B|C]]|$1|D|3|-4|5|E|7|H|8|@]|9|@]|A|$]]]|F|$]]

<pre><code>SELECT sum(isnull(TotalHoursM,0)) 
 + isnull(TotalHoursT,0) 
 + isnull(TotalHoursW,0) 
 + isnull(TotalHoursTH,0) 
 + isnull(TotalHoursF,0))
AS TOTAL FROM LeaveRequest
</code></pre>

blocks|key|1511073|text|看起来您想要对所有列求和(我不确定"sum+3列“从何而来)，而不仅仅是TotalHoursM，所以尝试如下：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1511074|SELECT+
++++SUM(++++ISNULL(TotalHoursM++,0)
++++++++++%2B+ISNULL(TotalHoursT++,0)
++++++++++%2B+ISNULL(TotalHoursW++,0)
++++++++++%2B+ISNULL(TotalHoursTH+,0)
++++++++++%2B+ISNULL(TotalHoursF++,0)+
+++++++)+AS+TOTAL
++++FROM+LeaveRequest|code-block|syntax|javascript|1511075|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

looks like you want to SUM all the columns (I'm not sure where "sum 3 columns" comes from), not just TotalHoursM, so try this:

<pre><code>SELECT 
 SUM( ISNULL(TotalHoursM ,0)
 + ISNULL(TotalHoursT ,0)
 + ISNULL(TotalHoursW ,0)
 + ISNULL(TotalHoursTH ,0)
 + ISNULL(TotalHoursF ,0) 
 ) AS TOTAL
 FROM LeaveRequest
</code></pre>

blocks|key|1511136|text|前面使用ISNULL函数的答案仅适用于MS+Sql+Server。COALESCE函数也可以在SQL+Server中使用。但是也可以在标准的SQL数据库系统中工作。在给定的示例中：|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|1511137|SELECT+sum(COALESCE(TotalHoursM,0))
++++++++++%2B+COALESCE(TotalHoursT,0)
++++++++++%2B+COALESCE(TotalHoursW,0)
++++++++++%2B+COALESCE(TotalHoursTH,0)
++++++++++%2B+COALESCE(TotalHoursF,0)+AS+TOTAL+FROM+LeaveRequest|code-block|syntax|javascript|1511138|这与ISNULL解决方案相同，唯一的区别是函数的名称。两者都可以在SQL+Server中工作，但COALESCE是ANSI标准，而ISNULL不是。而且，COALESCE更加灵活。ISNULL只能使用两个参数。如果第一个参数为空，则返回第二个参数的值，否则返回第一个参数的值。COALESCE将接受2到'n‘(我不知道’n‘的限制)参数，并返回第一个非NULL参数的值。当只有两个参数时，效果与ISNULL相同。|1511139|entityMap^0|4|6|X|8|0|0|2|6|1C|8|1T|6|25|8|2I|6|4W|4|5H|6|0^^$0|@$1|2|3|4|5|6|7|O|8|@$9|P|A|Q|B|C]|$9|R|A|S|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|T|8|@]|D|@]|E|$I|J]]|$1|K|3|L|5|6|7|U|8|@$9|V|A|W|B|C]|$9|X|A|Y|B|C]|$9|Z|A|10|B|C]|$9|11|A|12|B|C]|$9|13|A|14|B|C]|$9|15|A|16|B|C]|$9|17|A|18|B|C]]|D|@]|E|$]]|$1|M|3|-4|5|6|7|19|8|@]|D|@]|E|$]]]|N|$]]

The previous answers using the <code>ISNULL</code> function are correct only for MS Sql Server. The <code>COALESCE</code> function will also work in SQL Server. But will also work in standard SQL database systems. In the given example:

<pre><code>SELECT sum(COALESCE(TotalHoursM,0))
 + COALESCE(TotalHoursT,0)
 + COALESCE(TotalHoursW,0)
 + COALESCE(TotalHoursTH,0)
 + COALESCE(TotalHoursF,0) AS TOTAL FROM LeaveRequest
</code></pre>

This is identical to the <code>ISNULL</code> solution with the only difference being the name of the function. Both work in SQL Server but, <code>COALESCE</code> is ANSI standard and <code>ISNULL</code> is not. Also, <code>COALESCE</code> is more flexible.
<code>ISNULL</code> will only work with two parameters. If the first parameter is NULL then the value of the second parameter is returned, else the value of the first is returned.
COALESCE will take 2 to 'n' (I don't know the limit of 'n') parameters and return the value of the first parameter that is not <code>NULL</code>. When there are only two parameters the effect is the same as <code>ISNULL</code>.

blocks|key|1511153|text|我会试试这个：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1511154|select+sum+(case+when+TotalHousM+is+null+then+0+else+TotalHousM+end)
+++++++%2B+(case+when+TotalHousT+is+null+then+0+else+TotalHousT+end)
+++++++%2B+(case+when+TotalHousW+is+null+then+0+else+TotalHousW+end)
+++++++%2B+(case+when+TotalHousTH+is+null+then+0+else+TotalHousTH+end)
+++++++%2B+(case+when+TotalHousF+is+null+then+0+else+TotalHousF+end)
+++++++as+Total
From+LeaveRequest|code-block|syntax|javascript|1511155|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

I would try this:

<pre><code>select sum (case when TotalHousM is null then 0 else TotalHousM end)
 + (case when TotalHousT is null then 0 else TotalHousT end)
 + (case when TotalHousW is null then 0 else TotalHousW end)
 + (case when TotalHousTH is null then 0 else TotalHousTH end)
 + (case when TotalHousF is null then 0 else TotalHousF end)
 as Total
From LeaveRequest
</code></pre>

blocks|key|1511230|text|Just+for+reference,+the+equivalent+statement+for+MySQL+is:+IFNull(Column,0)。|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|1511231|如果不为null，则此语句的计算结果为列值，否则计算结果为0。|1511232|entityMap|0|LINK|mutability|MUTABLE|url|http://dev.mysql.com/doc/refman/5.0/en/control-flow-functions.html#function_ifnull^0|0|23|0|0|0^^$0|@$1|2|3|4|5|6|7|N|8|@]|9|@$A|O|B|P|1|Q]]|C|$]]|$1|D|3|E|5|6|7|R|8|@]|9|@]|C|$]]|$1|F|3|-4|5|6|7|S|8|@]|9|@]|C|$]]]|G|$H|$5|I|J|K|C|$L|M]]]]

<a href="http://dev.mysql.com/doc/refman/5.0/en/control-flow-functions.html#function_ifnull" rel="noreferrer">Just for reference, the equivalent statement for MySQL is: IFNull(Column,0)</a>.

This statement evaluates as the column value if not null, otherwise it is evaluated as 0.

blocks|key|1296669|text|如果希望避免空值，请使用IsNull(Column，1)|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1296670|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|$]]

If you want to avoid the null value use IsNull(Column, 1)

blocks|key|1511295|text|您也可以使用nvl(Column,0)|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|1511296|entityMap^0|6|D|0^^$0|@$1|2|3|4|5|6|7|H|8|@$9|I|A|J|B|C]]|D|@]|E|$]]|$1|F|3|-4|5|6|7|K|8|@]|D|@]|E|$]]]|G|$]]

You can also use <code>nvl(Column,0)</code>

<pre><code>SELECT 
 sum(TotalHoursM)
 + (TotalHoursT)
 + (TotalHoursW)
 + (TotalHoursTH)
 + (TotalHoursF) 
 AS TOTAL
FROM LeaveRequest
</code></pre>

SQL: sum 3 columns when one column has a null value?

SQL数据库

SQL Server

数据库

SELECT     sum(TotalHoursM)          + (TotalHoursT)          + (TotalHoursW)          + (TotalHoursTH)          + (TotalHoursF)           AS TOTALFROM LeaveReq...

问SQL:当一列有空值时求和3列？
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问SQL:当一列有空值时求和3列？EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问SQL:当一列有空值时求和3列？
EN