[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2012-08-15
4
Medium Priority
?
842 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
0
Comment
Question by:Mehawitchi
  • 2
  • 2
4 Comments
 
LVL 16

Accepted Solution

by:
EvilPostIt earned 2000 total points
ID: 38295469
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

0
 

Author Comment

by:Mehawitchi
ID: 38295566
Thank you Evilpostit - I'll check your solution and revert with my comments
0
 
LVL 16

Expert Comment

by:EvilPostIt
ID: 38295576
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

0
 

Author Closing Comment

by:Mehawitchi
ID: 38333965
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
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Written by Valentino Vranken. Introduction: The first step of creating a SQL Server Reporting Services (SSRS) report involves setting up a connection to the data source and programming a dataset to retrieve data from that data source.  The data…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …

825 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question