如何保护 sql 语句免于除以零错误 [英] How to protect sql statement from Divide By Zero error

问题描述

我正在创建一些报告，这些报告采用有限总数(假设为 2,500)的产品(在此示例中假设为冰淇淋蛋筒)，并计算其中有多少在上菜前已损坏.

I'm in the process of creating some reports that take a finite total (lets say 2,500) of products (for this example lets say Ice Cream Cones) and counts how many of them were broken before serving.

现在我得到了破碎锥体的实际计数代码.

Now the actual count code of broken cones I've got down.

SELECT COUNT(broken_cones) FROM [ice].[ice_cream_inventory] 
WHERE broken_cones = 'Yes'

但是，我还需要从这个总数中提取一定比例的破碎锥体.我一直在玩代码，但我一直在使用下面的代码遇到除以零"错误.

However, I need a percentage of broken cones from this total as well. I've been playing around with the code but I keep running into a 'Divide By Zero' error with this code below.

SELECT CAST(NULLIF((.01 * 2500)/Count(broken_cones), 0) AS 
decimal(7,4)) FROM [ice].[ice_cream_inventory] WHERE broken_cones = 'Yes'

就目前而言，没有任何破碎的锥体(并且不会有一段时间)，因此现在总数为零.如何将 NULL 场景显示为零?

For right now, there aren't any broken cones (and won't be for a while) so the total right now is zero. How can I show the NULL scenario as zero?

我试图在混合中放置一个 ISNULL 语句，但我一直收到除以零"错误.我这样做对吗?

I tried to place an ISNULL statement in the mix but I kept getting the 'Divide by Zero' error. Am I even doing this right?

::edit::

这就是我的结局.

SELECT 
CASE
WHEN COUNT(broken_cones) = 0 then 0
ELSE CAST(NULLIF((.01 * 2500)/Count(broken_cones), 0) AS decimal(7,4))
END
FROM [ice].[ice_cream_inventory] WHERE broken_cones = 'Yes'

推荐答案

使用 case 语句.

Use a case statement.

SELECT 
CASE WHEN COUNT(broken_cones) = 0 then 0
ELSE CAST(NULLIF((.01 * 2500)/Count(broken_cones), 0) AS decimal(7,4)) END
FROM [ice].[ice_cream_inventory] WHERE broken_cones = 'Yes'

这篇关于如何保护 sql 语句免于除以零错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！