CODEWITHSUNDEEP
sqlsql-server-2008t-sqlsql-server-2005
Denys Wessels
Denys Wessels

Reputation: 17039

Simple way to prevent a Divide By Zero error in SQL

I have a SQL query which used to cause a

Divide By Zero exception

I've wrapped it in a CASE statement to stop this from happening. Is there a simpler way of doing this?

Here's my code:

Percentage =  CASE WHEN AttTotal <> 0 THEN (ClubTotal/AttTotal) * 100 ELSE 0 END

Upvotes: 16

Views: 116434

Answers (5)

Jason
Jason

Reputation: 1

The solution that I found to handle the divide by zero problem is to create a function that I can call upon to deal the situation, as I often have to perform some sort of ratio/ percentage type analysis. Here's the simple function that I wrote.

Create Function fnRatio(@Numerator decimal(10,2),@Demoninator decimal(10,2))

Returns decimal(10,2)

Begin

Return

Case 

      When @Demoninator = 0 then 0.00 



      When @Demoninator Is Null then Null



Else

      @Numerator/@Demoninator

End

End

Regards

Jason

Upvotes: 0

PiotrWolkowski
PiotrWolkowski

Reputation: 8782

I'm using NULLIF bit differently, because in some cases I do have to return some value. Usually I need to return 0 when there is a divide by zero error. In that case I wrap whole expression in ISNULL. So it would be:

Percentage =  ISNULL(100 * ClubTotal / NULLIF(AttTotal, 0), 0)

The inner part is evaluated to NULL and then ISNULL replaces it with 0.

Upvotes: 9

Thorsten Kettner
Thorsten Kettner

Reputation: 94913

In my opinion the CASE statement is exactly the way to go. Rather than calculating something you state the value to return for the case that AttTotal is zero. You could even add another case branch for 0 of 0 being 100%.

Just a side note: I would not return 0 when AttTotal is zero and ClubTotal is greater than zero. NULL might be more appropriate. Or you would create strings (e.g. '10.50%') rather than numbers (e.g. 10.5%), containing "No att. total" in case AttTotal is zero:

PercentageString :=
  CASE
    WHEN AttTotal = 0 AND ClubTotal = 0 then '100%'
    WHEN AttTotal = 0 AND ClubTotal <> 0 THEN 'No att. total'
    ELSE to_char(ClubTotal / AttTotal * 100) || '%'
  END;

Upvotes: 1

Tom Chantler
Tom Chantler

Reputation: 14941

A nicer way of doing this is to use NULLIF like this:

Percentage =  100 * ClubTotal / NULLIF(AttTotal, 0)

Upvotes: 46

i-one
i-one

Reputation: 5120

Percentage =  IsNull(ClubTotal/NullIf(AttTotal, 0) * 100, 0)

Upvotes: 5

Related Questions