Dived By Zero Error - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Dived By Zero Error

Author

Topic

dhinasql
Posting Yak Master

195 Posts

Posted - 2008-11-19 : 23:34:32

Hello Friends,

I have the Query, used to find the average rating, But when the total number of votes for the particular data, it will show the divide by zero error.. I want to fix the divide by zero, if the divide by zero appears i need to return the average rating as Zero

Please modify my Query

SELECT Top 2 rh.LinkId,r.RefId,r.CatId,rh.Title,rh.URL,rh.Description,rr.noofvoter,rr.Points,rr.avgrating,rr.Rating FROM Kr_Reference r INNER JOIN Kr_ReferenceHistory rh ON rh.RefId=r.RefId AND rh.Status=1 AND rh.Abusive=0 AND r.Status=1 AND r.catid=14 INNER JOIN (SELECT RefId,SUM(Points)AS Points,SUM(CASE WHEN Points=0 THEN 0 ELSE 1 END) AS noofvoter,SUM(Points)/COUNT(RatId) AS Rating,SUM(Points)*1.0/SUM(CASE WHEN Points=0 THEN 0 ELSE 1 END) AS avgrating FROM Kr_ReferenceRating GROUP BY RefId) rr ON rr.RefId=r.RefId

Here i have the problem in this part

SUM(Points)*1.0/SUM(CASE WHEN Points=0 THEN 0 ELSE 1 END) AS avgrating

So if the SUM(CASE WHEN Points=0 THEN 0 ELSE 1 END) is Zero the divide by zero error will occure.

So in this case i have to return the avgrating as 0.

Please help me to fix this.

Thanks in Advance friends..

cvraghu
Posting Yak Master

187 Posts

Posted - 2008-11-19 : 23:42:04

I don't understand your logic of average. But you can use the same CASE statement like this -

CASE WHEN Points=0 THEN 0
ELSE SUM(Points)*1.0/SUM(1)
END AS avgrating

dhinasql
Posting Yak Master

195 Posts

Posted - 2008-11-20 : 00:13:22

Dear CVRAGHU,

Thank you for your reply,

I need the query to be modified

When the SUM(CASE WHEN Points=0 THEN 0 ELSE 1 END) is 0 then it should return the value as 1.

Please modify this in my query it will be much appreciated all your helps.

SELECT Top 2 rh.LinkId,r.RefId,r.CatId,rh.Title,rh.URL,rh.Description,rr.noofvoter,rr.Points,rr.avgrating,rr.Rating FROM Kr_Reference r INNER JOIN Kr_ReferenceHistory rh ON rh.RefId=r.RefId AND rh.Status=1 AND rh.Abusive=0 AND r.Status=1 AND r.catid=14 INNER JOIN (SELECT RefId,SUM(Points)AS Points,SUM(CASE WHEN Points=0 THEN 0 ELSE 1 END) AS noofvoter,SUM(Points)/COUNT(RatId) AS Rating,SUM(Points)*1.0/SUM(CASE WHEN Points=0 THEN 0 ELSE 1 END) AS avgrating FROM Kr_ReferenceRating GROUP BY RefId) rr ON rr.RefId=r.RefId

Thanks in Advance

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-20 : 00:21:25

[code]ISNULL(SUM(Points)*1.0/NULLIF(SUM(CASE WHEN Points=0 THEN 0 ELSE 1 END),0),1)[/code]

dhinasql
Posting Yak Master

195 Posts

Posted - 2008-11-20 : 01:23:36

Visakh16,

Thank you so much, It works fine..

Thanks lot

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-20 : 01:30:40

Welcome

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-20 : 02:09:16

SUM(CASE WHEN Points = 0 THEN 0 ELSE 1 END)

is equal to SUM(SIGN(ABS(Points)))
If Points are positive values only, this is equal to first statement SUM(SIGN(Points))

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources