我正在尝试在 SQL 中运行一个脚本,在其中进行除法,但出现错误“遇到除以零错误”
我尝试添加 NULLIF,这是我读过的一篇旧帖子中建议的,但这并没有帮助。
SELECT SUM([SE Gross]) AS [Donors Gross],
ROUND((SUM([Cancelled]) * 100) / NULLIF(SUM([SE Gross]), 2), 0),
SUM([Venue Cost]),
SUM([Accom/Expenses]),
ROUND(SUM([SE Gross]) / NULLIF(SUM([fundraising days]), 2), 0) AS Column1,
ROUND(SUM([Venue Cost]) / NULLIF(SUM([SE Gross]), 2), 0) AS Column2,
ROUND(SUM([Venue Cost] + [Accom/Expenses]) / NULLIF(SUM([SE Gross]), 2), 0) AS column3,
ROUND(SUM([Venue Cost] + [Accom/Expenses]) / NULLIF(SUM([donor net COUNT]), 2), 0) AS column4
FROM dbo.TEST1;
我目前得到的结果是:除以零遇到错误。
NULL
如果它等于 2,您正在尝试转换为。检查这部分:NULLIF(SUM([SE Gross]), 2)
,您必须在以下内容中更改2
为0
:
SELECT SUM([SE Gross]) AS [Donors Gross],
ROUND((SUM([Cancelled]) * 100) / NULLIF(SUM([SE Gross]), 0), 0),
SUM([Venue Cost]),
SUM([Accom/Expenses]),
ROUND(SUM([SE Gross]) / NULLIF(SUM([fundraising days]), 0), 0) AS Column1,
ROUND(SUM([Venue Cost]) / NULLIF(SUM([SE Gross]), 0), 0) AS Column2,
ROUND(SUM([Venue Cost] + [Accom/Expenses]) / NULLIF(SUM([SE Gross]), 0), 0) AS column3,
ROUND(SUM([Venue Cost] + [Accom/Expenses]) / NULLIF(SUM([donor net COUNT]), 0), 0) AS column4
FROM dbo.TEST1;
此外,CASE
如果divider = 0 ,您可以使用语句来避免整个条目为空,如下所示:
CASE WHEN NULLIF(SUM([SE Gross]), 0) = 0 THEN 1 ELSE NULLIF(SUM([SE Gross]), 0) END
或组合ISNULL
与NULLIF
下面:
ISNULL(NULLIF(SUM([SE Gross]), 0), 1)
我的猜测是你想四舍五入到小数点后两位,所以在这种情况下,它应该是这样的:
ROUND((SUM([Cancelled]) * 100) / ISNULL(NULLIF(SUM([SE Gross]), 0), 1), 2)
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句