Simple way to prevent a Divide By Zero error in SQL

Question

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

It depends on how you wan't your query and result to behave when `AttTotal` is 0 or NULL, so there is no general way of doing it. I'd say your query is just fine. Even if you write your query differently using `ISNULL` or `NULLIF` at the end it will likely execute the same. — Nenad Zivkovic, Oct 28 '13 at 09:34
As has been pointed out by others, the logic in this CASE statement doesn't quite make sense to us. See my comment on my answer for a brief explanation and then choose whether you want your original answer or mine. My answer would be a more usual solution. — Tom Chantler, Oct 28 '13 at 09:40
Possible duplicate of [How to avoid the "divide by zero" error in SQL?](http://stackoverflow.com/questions/861778/how-to-avoid-the-divide-by-zero-error-in-sql) — Henrik Staun Poulsen, Mar 10 '16 at 21:06

score 46 · Accepted Answer · answered Oct 28 '13 at 09:25

46

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

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

answered Oct 28 '13 at 09:25

Tom Chantler

14,753
4
48
53

4

This will return `NULL` if `AttTotal = 0`, while the query in question returns 0. – Nenad Zivkovic Oct 28 '13 at 09:31
4

@NenadZivkovic - Returning `NULL` for a divide by zero error probably makes more mathematical sense anyway though. – Martin Smith Oct 28 '13 at 09:34
I agree with @Martin Smith. If you have a situation where the ClubTotal is a number and AttTotal is zero, it doesn't make sense to return zero. Or any other number :-) Returning zero implies ClubTotal is zero and AttTotal is more than zero. – Tom Chantler Oct 28 '13 at 09:38

score 9 · Answer 2 · answered Mar 31 '15 at 17:05

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.

score 5 · Answer 3 · answered Oct 28 '13 at 09:24

5

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

answered Oct 28 '13 at 09:24

i-one

5,050
1
28
40

Not quite the same as the query in the question. If the value of `AttTotal` or `ClubTotal` are `NULL` this will now also return `0`. – Martin Smith Oct 28 '13 at 09:25
1

Agree, I like the one by @Dommer, despite it does not retain original logic too. – i-one Oct 28 '13 at 09:30

score 1 · Answer 4 · answered Oct 28 '13 at 09:48

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;

score 0 · Answer 5 · edited May 06 '15 at 11:02

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

Simple way to prevent a Divide By Zero error in SQL

5 Answers5

Linked