We help IT Professionals succeed at work.

How to Avoid "Divide by Zero" error - T-SQL

Medium Priority
868 Views
Last Modified: 2012-08-26
Hello Experts,

I'm getting a "Divided by zero" error when running a report using the the following select statement:

SELECT CTE1.ChID, CTE1.ChName, CTE1.GroupRank, CTE1.ChRank, CTE1.StartTime TimeSlot, CTE1.FromDate, AVG(CTE1.Rating) Rating,AVG(CTE1.TRP) TRP,AVG((CTE1.TRP/CTE2.TRP) *100) AS Share
FROM CTE1 INNER JOIN CTE2 on CTE1.FromDate = CTE2.FromDate and CTE1.SheetDay = CTE2.SheetDay and CTE1.SlotOrder = CTE2.SlotOrder

Obviuosly, the error is coming from this part:
AVG((CTE1.TRP/CTE2.TRP) *100) AS Share

Therefore, I need to add a function that will ignore the error. Something like isNull() or isZero(), or whatever...

Appreciate your help
Comment
Watch Question

IT Director
CERTIFIED EXPERT
Commented:
Dependant upon which column is providing the 0, I have just assumed CTE1.TRP although this may be wrong.
SELECT 
CTE1.ChID, 
CTE1.ChName, 
CTE1.GroupRank, 
CTE1.ChRank, 
CTE1.StartTime TimeSlot, 
CTE1.FromDate, 
AVG(CTE1.Rating) Rating,
AVG(CTE1.TRP) TRP,
CASE CTE1.TRP
WHEN 0 THEN 0
ELSE AVG((CTE1.TRP/CTE2.TRP) *100) 
END AS Share
FROM CTE1 
INNER JOIN CTE2 on CTE1.FromDate = CTE2.FromDate and CTE1.SheetDay = CTE2.SheetDay and CTE1.SlotOrder = CTE2.SlotOrder

Open in new window

Author

Commented:
Thank you Evilpostit - I'll check your solution and revert with my comments
EvilPostItIT Director
CERTIFIED EXPERT

Commented:
On the other hand if you did not want to show these values you could just have

WHERE NOT CTE1.TRP=0

Open in new window

Author

Commented:
So sorry EvilPostIt for my latttttttttttttttttttttttttttte reply.

We had a long Eid break (following the holy month of Ramadan), and I wasn't able to work nor test your solution.

Finally I had time today, and after a bit of tweaking it worked like charm. Many thanks and sorry again.

Best,
Hani

Explore More ContentExplore courses, solutions, and other research materials related to this topic.