How to protect sql statement from Divide By Zero error

Question

I am going to create several reports that take the final result (say 2500) of products (in this example, Ice Cream Cones) and calculate how many of them were broken before serving.

Now the actual account code of the broken cones is mine.

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

However, I also need a percentage of broken cones. I played with the code, but I continue to work with the "Divide By Zero" error using 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'

There are currently no broken cones (and will not be for a while), so now the full value is zero. How to show null script as zero?

I tried putting the ISNULL statement in the mix, but I kept getting the "Divide by Zero" error. Am I even doing this right?

:: edit ::

That's what I did.

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

case.

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

, .

NULLIF , . , NULLIF. .

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

Answer 3

NULLIF() - , , NULL, null. :

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

, divide NULLIF(), . , - NULL, COUNT() :

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

, , NULL ? ISNULL():

ISNULL(<expression1>, <expression2>)

If the first expression is NULL, return the second expression, so our SQL will now be as follows:

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