How to avoid the "divide by zero" error in SQL?

This seemed to be the best fix for my situation when trying to address dividing by zero, which does happen in my data.
-------------------------------------------------------------------------------------------
Suppose you want to calculate the male–female ratios for various school clubs, but you discover that the following query fails and issues a divide-by-zero error when it tries to calculate ratio for the Lord of the Rings Club, which has no women:

SELECT club_id, males, females, males/females AS ratio
FROM school_clubs;

You can use the function `NULLIF` to avoid division by zero. `NULLIF` compares two expressions and returns null if they are equal or the first expression otherwise.

Rewrite the query as:

SELECT club_id, males, females, males/NULLIF(females, 0) AS ratio
FROM school_clubs;

Any number divided by `NULL` gives `NULL`, and no error is generated.

For update SQLs:

update Table1 set Col1 = Col2 / ISNULL(NULLIF(Col3,0),1)

You can at least stop the query from breaking with an error and return `NULL` if there is a division by zero:

SELECT a / NULLIF(b, 0) FROM t

However, I would **NEVER** convert this to Zero with `coalesce` like it is shown in that other answer which got many upvotes. This is completely wrong in a mathematical sense, and it is even dangerous as your application will likely return wrong and misleading results.

CREATE FUNCTION dbo.Divide(@Numerator Real, @Denominator Real)
RETURNS Real AS
/*
Purpose: Handle Division by Zero errors
Description: User Defined Scalar Function
Parameter(s): @Numerator and @Denominator

Test it:

SELECT 'Numerator = 0' Division, dbo.fn_CORP_Divide(0,16) Results
UNION ALL
SELECT 'Denominator = 0', dbo.fn_CORP_Divide(16,0)
UNION ALL
SELECT 'Numerator is NULL', dbo.fn_CORP_Divide(NULL,16)
UNION ALL
SELECT 'Denominator is NULL', dbo.fn_CORP_Divide(16,NULL)
UNION ALL
SELECT 'Numerator & Denominator is NULL', dbo.fn_CORP_Divide(NULL,NULL)
UNION ALL
SELECT 'Numerator & Denominator = 0', dbo.fn_CORP_Divide(0,0)
UNION ALL
SELECT '16 / 4', dbo.fn_CORP_Divide(16,4)
UNION ALL
SELECT '16 / 3', dbo.fn_CORP_Divide(16,3)

*/
BEGIN
RETURN
CASE WHEN @Denominator = 0 THEN
NULL
ELSE
@Numerator / @Denominator
END
END
GO

Use `NULLIF(exp,0)` but in this way - `NULLIF(ISNULL(exp,0),0)`

`NULLIF(exp,0)` breaks if exp is `null` but `NULLIF(ISNULL(exp,0),0)` will not break

Replacing "divide by zero" with zero is controversial - but it's also not the only option. In some cases replacing with 1 is (reasonably) appropriate. I often find myself using

ISNULL(Numerator/NULLIF(Divisor,0),1)


when I'm looking at shifts in scores/counts, and want to default to 1 if I don't have data. For example

NewScore = OldScore * ISNULL(NewSampleScore/NULLIF(OldSampleScore,0),1)

More often than not, I've actually calculated this ratio somewhere else (not least because it can throw some very large adjustment factors for low denominators. In this case I'd normally control for OldSampleScore is greater than a threshold; which then precludes zero. But sometimes the 'hack' is appropriate.

SELECT Dividend / ISNULL(NULLIF(Divisor,0), 1) AS Result from table

By catching the zero with a nullif(), then the resulting null with an isnull() you can circumvent your divide by zero error.

Sometimes, 0 might not be appropriate, but sometimes 1 is also not appropriate. Sometimes a jump from 0 to 100,000,000 described as 1 or 100-percent change might also be misleading. 100,000,000 percent might be appropriate in that scenario. It depends on what kind of conclusions you intend to draw based on the percentages or ratios.

For example, a very small-selling item moving from 2-4 sold and a very large-selling item changing from 1,000,000 to 2,000,000 sold might mean very different things to an analyst or to management, but would both come through as 100% or 1 change.

It might be easier to isolate NULL values than to scour over a bunch of 0% or 100% rows mixed with legitimate data. Often, a 0 in the denominator can indicate an error or missing value, and you might not want to just fill in an arbitrary value just to make your dataset look tidy.

CASE
WHEN [Denominator] = 0
THEN NULL --or any value or sub case
ELSE [Numerator]/[Denominator]
END as DivisionProblem

In order to avoid a "Division by zero" error we have programmed it like this:

Select Case when divisor=0 then null
Else dividend / divisor
End ,,,


But here is a much nicer way of doing it:

Select dividend / NULLIF(divisor, 0) ...

Now the only problem is to remember the NullIf bit, if I use the "/" key.

This is how I fixed it:

IIF(ValueA != 0, Total / ValueA, 0)

It can be wrapped in an update:

SET Pct = IIF(ValueA != 0, Total / ValueA, 0)

Or in a select:

SELECT IIF(ValueA != 0, Total / ValueA, 0) AS Pct FROM Tablename;

Thoughts?