How to protect sql statement from Divide By Zero e

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

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'`

Answer 2

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 3

The NULLIF() function is a great way to prevent divide by zero, since anything divide by NULL returns null. The way to use it is as follows:

<expression> / NULLIF( <expression>, 0 )

Unfortunately you've wrapped your whole divide expression in NULLIF() which is why it isn't working for you. So step one is to get it to return NULL if your COUNT() comes back zero:

SELECT
    (0.01 * 2500) / NULLIF( COUNT(broken_cones), 0 )
FROM [ice].[ice_cream_inventory]
WHERE broken_cones = 'Yes'

Now you said you wanted that NULL to come back zero? That is where you use ISNULL():

ISNULL(<expression1>, <expression2>)

If the first expression is NULL then return the second expression, so our SQL now becomes:

SELECT 
    ISNULL(
        (0.01 * 2500) / NULLIF( COUNT(broken_cones), 0 ),
        0
    )
FROM [ice].[ice_cream_inventory]
WHERE broken_cones = 'Yes'