How to protect sql statement from Divide By Zero error

Question

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'

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?

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::

Here's what I ended up with.

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'

Answer 1

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'

Answer 2

You already have a solution, but this is why your original solution didn't work.

Your NULLIF needs to be moved in order to be effective. It is doing the division before it gets to the NULLIF call. Dividing by null will return a null value.

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