## divided by zero error

 Author Message jess. Grasshopper Group: General Forum Members Points: 14 Visits: 41 Hello,I am reviving this error when trying to run a query(48 row(s) affected)Dec 25 2011 3:30AMJan 1 2012 3:30AM(5 row(s) affected)Msg 8134, Level 16, State 1, Line 64Divide by zero error encountered.This is the code giving me trouble:select case when r1.su is null then r2.su when r1.su != r2.su then r1.su+'~'+r2.su else r1.suend as su,case when r1.cur is null then r2.cur when r1.cur != r2.cur then r1.cur+'~'+r2.cur else r1.curend as cur,r1.dname,r1.rate,r2.dname,r2.rate,round(((r1.rate-r2.rate)/(r1.rate+r2.rate))*100.0,2) as percthis is how I tried to fix it:select case when r1.su is null then r2.su when r1.su != r2.su then r1.su+'~'+r2.su else r1.suend as su,case when r1.cur is null then r2.cur when r1.cur != r2.cur then r1.cur+'~'+r2.cur else r1.curend as cur,r1.dname,r1.rate,r2.dname,r2.rate,case when (r1.rate+r2.rate) = 0 then 0 else round(((r1.rate-r2.rate)/(r1.rate+r2.rate))*100.0,2) end as percAny advice as to what I may be doing wrong?thanks,.jess Mark Cowne SSCrazy Group: General Forum Members Points: 2093 Visits: 22797 Try changing`case when (r1.rate+r2.rate) = 0 then 0 else round(((r1.rate-r2.rate)/(r1.rate+r2.rate))*100.0,2) end as perc`to`isnull(round(((r1.rate-r2.rate)/(nullif(r1.rate+r2.rate,0)))*100.0,2),0) as perc` ____________________________________________________Deja View - The strange feeling that somewhere, sometime you've optimised this query beforeHow to get the best help on a forumhttp://www.sqlservercentral.com/articles/Best+Practices/61537 jess. Grasshopper Group: General Forum Members Points: 14 Visits: 41 I'm still getting the same error when using that. Mark Cowne SSCrazy Group: General Forum Members Points: 2093 Visits: 22797 Can you post some DDL and sample data? ____________________________________________________Deja View - The strange feeling that somewhere, sometime you've optimised this query beforeHow to get the best help on a forumhttp://www.sqlservercentral.com/articles/Best+Practices/61537 GSquared SSChampion Group: General Forum Members Points: 14375 Visits: 9729 What datatypes are the rate columns? Float? - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETCProperty of The Thread"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon jess. Grasshopper Group: General Forum Members Points: 14 Visits: 41 This has been fixed. Basically, there were a few other divisors in the query that I didn't check as I thought it had something to do with that section alone.I added case statements to the other divisors and it worked.Thanks for your help!jess.