如何避免错误8134级别16 [英] How to avoid Error 8134 level 16

问题描述

嗨
我有这个Tsql语句，但我收到了上述（问题）错误。



我想获得Amount1和Amount1之间的百分比差异金额2



我知道我必须使用 CASE声明，但我没有把它弄好。



我的陈述：

  SELECT  
 [Projects]，
 Sum（[Amount1]） AS  [Total_Amount1]，
 Sum（[Amount2]） AS  [Total_Amount2] ,, 
总和（[Amount1]） -  Sum（[Amount2]） AS  [ DELTA]，
 
总和（[Amount1]） -  Sum（[Amount2]）/ Sum（[Amount2]）*  100   AS  [ABWEICHUNG]，
 
总和（[Amount3]） AS  [Total_Amount3]，
总和（[Amount4]） AS  [Total_Amount4] 
  FROM  tbl1 
  GROUP   BY  [Projects] 

谢谢

解决方案

8134除以零。您可以添加一个case语句来检查SUM（Amount2）是0的时间并以不同的方式处理它，或者我过去做的事情是这样的：



< pre lang =SQL> Sum（[Amount2]）/（Sum（[Amount2] +0。 0000001 ））

这将始终避免除以零，并添加少量可能不会影响任何结果，具体取决于您的数据。

错误8134是除以零 - 在问题中包含该信息会很有帮助！



发生错误是因为 Sum（Amount2）为至少一个组返回 0 。您可以使用 NullIf 0 转换为 NULL 来避免错误c>功能。



你还需要围绕总和（Amount1）括号 - Sum（Amount2） ，因为除法运算符的优先级高于减法运算符。

 Sum（Amount1） -  Sum（Amount2）/ Sum（Amount2）
 ===总和（金额1） - （总和（金额2）/总和（金额2））
 ===总和（金额1） -   1  

虽然技术上正确，但最后加上 * 100 会使计算更难以阅读。你是否将分子或分母乘以100并不是很明显。将乘法移动到开头会使这更清楚。



尝试使用：

  100  *（Sum（[Amount1]） -  Sum（[Amount2]））/  NullIf （Sum（[Amount2]）， 0 ） AS  [ ABWEICHUNG]，

此行抛出错误

 Sum（[Amount1]）=  0   -  Sum（[Amount2]）/ Sum（[Amount2]）*  100   AS  [ABWEICHUNG]，

它应该是..

（< span class =code-digit> 0   -  Sum（[Amount2]）/ Sum（[Amount2]）*  100 ） AS  [ABWEICHUNG]，

请详细说明你的问题......你需要什么结果

Hi I have this Tsql statement but I'm getting the above (question) error.

I wanna get the Percentage differance between Amount1 and Amount2

I learnt i have to use a CASE Statement but i ain't getting it right.

My statement:

SELECT 
[Projects],
Sum([Amount1]) AS [Total_Amount1], 
Sum([Amount2]) AS [Total_Amount2],,
Sum([Amount1]) - Sum([Amount2]) AS [DELTA],

Sum([Amount1]) - Sum([Amount2])/ Sum([Amount2]) * 100 AS [ABWEICHUNG],

Sum([Amount3]) AS [Total_Amount3],
Sum([Amount4]) AS [Total_Amount4]
FROM tbl1
GROUP BY [Projects]

Thanks

解决方案

8134 is a divide by zero. You can add a case statement to check when SUM(Amount2) is 0 and handle it differently or what I have done in the past is instead something like this:

Sum([Amount2])/ (Sum([Amount2]+0.0000001))

This will always avoid the divide by zero and adding that small amount likely won't affect any results, depending on your data.

Error 8134 is "divide by zero" - it would have been helpful to include that information in the question!

The error occurs because Sum(Amount2) is returning 0 for at least one group. You can avoid the error by converting 0 to NULL using the NullIf function.

You'll also need parentheses around the Sum(Amount1) - Sum(Amount2), since the division operator has higher precedence than the subtraction operator.

Sum(Amount1) - Sum(Amount2) / Sum(Amount2)
=== Sum(Amount1) - (Sum(Amount2) / Sum(Amount2))
=== Sum(Amount1) - 1

Although technically correct, putting the * 100 at the end makes the calculation more confusing to read. It's not immediately obvious whether you're multiplying the numerator or the denominator by 100. Moving the multiplication to the start would make this clearer.

Try using:

100 * (Sum([Amount1]) - Sum([Amount2])) / NullIf(Sum([Amount2]), 0) AS [ABWEICHUNG],

This line throws error

Sum([Amount1]) = 0 - Sum([Amount2])/ Sum([Amount2]) * 100 AS [ABWEICHUNG],

It should be..

(0 - Sum([Amount2])/ Sum([Amount2]) * 100) AS [ABWEICHUNG],

Please elaborate your question..what result exactly you need

这篇关于如何避免错误8134级别16的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！