SQLTeam.com | Weblogs | Forums

Divide by zero error


#1

Hello Techie,

may any one please help me how to avoid divide by zero error in sql 2014.

[AOP Profit Based On Average Cost]-AverageCostBasedProfit / (AverageCostBasedProfit
WHEN Denominator is 0 then output should be 1

Thanks for your support


#2

why? That is not mathematically accurate.


#3

You can try IF

IF denominator = 0 
    value = 1
ELSE
   value = equation

Notice this is an example and is not intended to work as coded, but with no information this is the best I could do.


#4
SELECT NULLIF(SUM([AOP Profit Based On Average Cost]- [AverageCostBasedProfit] / [AverageCostBasedProfit]),1)

#5

I am of the same opinion as @gbritton in that when the denominator is null, showing a value of 1 can be misleading. However, you know your business rules and your clients better than we do, so if you do want to accomplish that in the expression, you can do the following:

COALESCE
( 
	( [AOP Profit Based On Average Cost] - AverageCostBasedProfit ) 
	/
	NULLIF(AverageCostBasedProfit,0)
	, 1
)

The expression you posted has syntax errors in it, so I tried to correct those based on what I guessed it should be.


#6

Be very, very careful of divide by zero errors.
Before making them disappear make sure you fully understand the reason for the error and that it is valid to remove the error.
I am aware of a business which lost a lot of money by ignoring a divide by zero error.


#7

CASE WHEN AverageCostBasedProfit = 0 THEN 1 ELSE [AOP Profit Based On Average Cost]-AverageCostBasedProfit / AverageCostBasedProfit END


#8

that's just wrong! Since when was anything/0 = 1?


#9

I understand... provided the solution as per requirement mentioned...