divided by zero error

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » divided by zero error

divided by zero error

Rate Topic

Display Mode

Topic Options

Author

Message

jess.

Posted Wednesday, January 02, 2013 7:27 AM

Grasshopper

Group: General Forum Members
Last Login: Tuesday, January 29, 2013 8:17 AM
Points: 14, Visits: 41

Hello,
I am reviving this error when trying to run a query

(48 row(s) affected)
Dec 25 2011 3:30AM
Jan 1 2012 3:30AM

(5 row(s) affected)
Msg 8134, Level 16, State 1, Line 64
Divide 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.su
end as su,
case
when r1.cur is null then r2.cur
when r1.cur != r2.cur then r1.cur+'~'+r2.cur
else r1.cur
end as cur,r1.dname,r1.rate,r2.dname,r2.rate,round(((r1.rate-r2.rate)/(r1.rate+r2.rate))*100.0,2) as perc

this 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.su
end as su,
case
when r1.cur is null then r2.cur
when r1.cur != r2.cur then r1.cur+'~'+r2.cur
else r1.cur
end 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 perc

Any advice as to what I may be doing wrong?

thanks,
.jess

Post #1401832

Mark-101232

Posted Wednesday, January 02, 2013 7:34 AM

UDP Broadcaster

Group: General Forum Members
Last Login: Yesterday @ 2:28 PM
Points: 1,498, Visits: 18,142

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

isnull(round(((r1.rate-r2.rate)/(nullif(r1.rate+r2.rate,0)))*100.0,2),0)  as perc

____________________________________________________

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

Never approach a goat from the front, a horse from the rear, or a fool from any direction.

Post #1401836

jess.

Posted Wednesday, January 02, 2013 7:45 AM

Grasshopper

Group: General Forum Members
Last Login: Tuesday, January 29, 2013 8:17 AM
Points: 14, Visits: 41

I'm still getting the same error when using that.

Post #1401849

Mark-101232

Posted Wednesday, January 02, 2013 7:49 AM

UDP Broadcaster

Group: General Forum Members
Last Login: Yesterday @ 2:28 PM
Points: 1,498, Visits: 18,142

Can you post some DDL and sample data?

____________________________________________________

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

Never approach a goat from the front, a horse from the rear, or a fool from any direction.

Post #1401852

GSquared

Posted Wednesday, January 02, 2013 7:54 AM

SSCoach

Group: General Forum Members
Last Login: Monday, May 06, 2013 1:09 PM
Points: 15,439, Visits: 9,569

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, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Post #1401854

jess.

Posted Wednesday, January 02, 2013 8:51 AM

Grasshopper

Group: General Forum Members
Last Login: Tuesday, January 29, 2013 8:17 AM
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.

Post #1401881

« Prev Topic | Next Topic »

Permissions