## divided by zero error

 Author Message jess. SSC-Enthusiastic Group: General Forum Members Points: 112 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 SSChampion Group: General Forum Members Points: 14637 Visits: 26567 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. SSC-Enthusiastic Group: General Forum Members Points: 112 Visits: 41 I'm still getting the same error when using that. Mark Cowne SSChampion Group: General Forum Members Points: 14637 Visits: 26567 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 SSC Guru Group: General Forum Members Points: 138219 Visits: 9731 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. SSC-Enthusiastic Group: General Forum Members Points: 112 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.