SQL Server值除以零 [英] SQL Server Value divided by zero

问题描述

  SELECT  Sum（Da​​ilySum） as  DailySum，sum（Unified ） as 统一，
（Sum（Da​​ilySum）/ sum（统一）） AS  HPPL 
  FROM  ProductionEntry 

在数据库中，Unified的值为0.因此，当Unified为试图划分DailySum然后出现错误。两个字段的数据类型都是十进制的。请帮助我。

解决方案

至少有几种方法可以避免除以零错误。

1）NULLIF [ ^ ]函数 - 零替换为 NULL 。对 NULL 的任何操作都返回 NULL 总是;）

  SELECT  Field1，Field2，Field1 / NULLIF（Field2， 0 ） AS 结果
  FROM  TableName 

2） CASE ... END [ ^ ]声明 - 仅对非零值返回除数结果

  SELECT  Field1，Field2， CASE   WHEN  Field2 =  0  那么  0   ELSE  Field1 / Field2  END   AS 结果
  FROM  TableName 

3） WHERE [ ^ ]子句 - 在子查询中返回非零值;注意：忽略等于零的Field2数据！

  SELECT  Field1，Field2，Field1 / Field2  AS 结果
  FROM （
  SELECT  Field1，Field2 
  FROM  TableName 
  WHERE  Field2> 0 
） AS  A 

正如你所提到的那样，Unified为0而你正试图使用除法运算然后它会给你错误，因为我们不能将任何值除以0.同样如果你将任何值除以1然后你得到的答案是相同的值，所以在你的情况下你可以使用这种类型查询...

  SELECT  
 SUM（ DailySum） as  DailySum 
，SUM（统一） as 统一
，（SUM（DailySum）/ 
  CASE   WHEN  SUM（统一） ）>  0  那么 SUM（统一） ELSE  < span class =code-digit> 1   END  
） AS  HPPL 
  FROM  ProductionEntry 

我已检查过，如果Sum统一为0然后它将替换为1或如果它超过0然后它将使用实际值

通过放置案例来稍微更改您的查询声明。

  SELECT  Sum（Da​​ilySum）  as  DailySum，sum（统一） as 统一，
  case  当 sum（统一）！=  0 （Sum（Da​​ilySum）/ sum （统一）） else  '  NA'  end   AS  HPPL 
  FROM  ProductionEntry 

NA不适用于统一总和的情况0

SELECT Sum(DailySum)as DailySum, sum(Unified) as Unified,
(Sum(DailySum)/sum(Unified)) AS HPPL
FROM ProductionEntry

Here in database the value of Unified is 0. As a result when Unified is trying to divide DailySum then it arises error. Both of the field's datatype are decimal. Please help me.

解决方案

There is few ways at least to avoid 'divide by zero' error.
1) NULLIF[^] function - zero is replaced with NULL. Any operation on NULL returns NULL always ;)

SELECT Field1, Field2, Field1/NULLIF(Field2,0) AS Result
FROM TableName

2) CASE ... END[^] statement - returns divide result only for non-zero values

SELECT Field1, Field2, CASE WHEN Field2 = 0 THEN 0 ELSE Field1/Field2 END AS Result
FROM TableName

3) WHERE[^] clause - returns non-zero values in subquery; note: Field2 data equal to zero are ignored!

SELECT Field1, Field2, Field1/Field2 AS Result
FROM (
    SELECT Field1, Field2
    FROM TableName
    WHERE Field2>0
) AS A

as you mention that, Unified is 0 and you are trying to use it with divide operation then it will giving you error because we can not divide any value with 0. in the same way if you divide any value with 1 then you got answer as the same value, so in your case you can use this type of query...

SELECT
    SUM(DailySum)as DailySum
    ,SUM(Unified) as Unified
    ,(SUM(DailySum)/
        CASE WHEN SUM(Unified) > 0 THEN SUM(Unified) ELSE 1 END
    ) AS HPPL
FROM ProductionEntry

I have checked that, if Sum of Unified is 0 then it will replace with 1 or if it is more then 0 then it will use actual value

Change your query slightly by putting a case statement.

SELECT Sum(DailySum)as DailySum, sum(Unified) as Unified,
case when sum(Unified) != 0 (Sum(DailySum)/sum(Unified)) else 'NA' end AS HPPL
FROM ProductionEntry

NA is not applicable for cases when sum of unified is 0.

这篇关于SQL Server值除以零的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！