Sql error "Divide by zero error encountered" when the divisor is 0 [duplicate]

Question

I have an error coming up "Divide by zero error encountered." in SQL Server 2005. I understand that i have few rows that are are getting divided by Zero that results in this error. So i was wondering if can eliminate this error when the divisor is zero. if the divisor is 0, it should return 0. How can i do that?

sum(isnull(cast(S.S_AmountCollected as numeric(10, 2)), 0)) / sum(isnull(cast(S.AmountSold as numeric(10, 2)), 0))

Thank you!

Use case to detect the 0 value and not run the division. msdn.microsoft.com/en-us/library/ms181765.aspx — asawyer
– asawyer, Commented Feb 20, 2013 at 16:36

Mike Dinescu · Accepted Answer · 2013-02-20 16:36:42Z

1

Use a CASE statement:

CASE WHEN SUM(ISNULL(CAST(S.AmountSold as numeric(10, 2)), 0) = 0
        THEN 0
      ELSE 
        SUM(ISNULL(CAST(S.S_AmountCollected as numeric(10, 2)), 0)) 
           / 
        SUM(ISNULL(CAST(S.AmountSold as numeric(10, 2)), 0))
     END

answered Feb 20, 2013 at 16:36

Mike Dinescu

56k17 gold badges124 silver badges153 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Ivaylo Strandjev Over a year ago

I believe he has an error when the value is 0, not null, but the solution you show will still work with a small change

Mike Dinescu Over a year ago

yeah.. it was hard to untangle his one-liner.. :)

challengeAccepted Over a year ago

CASE works great! Thank you Miky! :-)

challengeAccepted Over a year ago

The page wont let me accept an answer for 10 minutes after the post. Hmmm :-)

Ivaylo Strandjev · Accepted Answer · 2013-02-20 16:37:09Z

0

You can not eliminate this error as an integer divided by 0 has undefined value. You will have to add an explicit check to have the behavior you expect.

answered Feb 20, 2013 at 16:37

Ivaylo Strandjev

71.4k18 gold badges130 silver badges183 bronze badges

Collectives™ on Stack Overflow

Sql error "Divide by zero error encountered" when the divisor is 0 [duplicate]

2 Answers 2

4 Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Linked

Related