# Thread: Division by Zero Error

1. Registered User
Join Date
Nov 2003
Posts
10

## Unanswered: Division by Zero Error

I am getting a division by zero error from the SQL below, I believe I need to utilize Cast because it could be a negative number and I need to be able to view 8 positions to the right of the dec. (0000.00000000). Any help would be greatly appreciated.

SELECT FromDate AS MonthOneDate,State,Description,SUM(Flowthru) AS CELCSumFT,SUM(TotalCount) AS CELCsumCNT,
(SUM(Flowthru)/SUM(TotalCount)) * 100 AS CELCFtPct,
RetailNum AS ARZSumFT,RetailDen AS
ARZSumCnt,(RetailNum/RetailDen) * 100 AS ARZFtPct,
((RetailNum/RetailDen) - SUM(Flowthru) /SUM(TotalCount)) / SQRT(((SUM(Flowthru) + RetailNum) / (SUM(TotalCount) +RetailDen)) * (1-(SUM(Flowthru)+RetailNum)/(SUM(TotalCount) + RetailDen)) * (1 / SUM(TotalCount)+1/RetailDen)) AS ZScoreMonth1
FROM pmMidwest33_BlueRed WHERE TotalCount <> 0 AND CLEC <>
'' AND Acna NOT IN ('TRE','TRD') AND STATE IN ('IL','IZ','TR','MI','RE')
GROUP BY FromDate,State,Description,RetailNum,RetailDen
Last edited by jgtorres; 11-09-03 at 03:08.

2. Registered User
Join Date
Oct 2003
Location
Switzerland
Posts
140
Did you figure what part of your SQL query returns a divide by zero ? It could be :

sum(TotalCount),
RetailDen,
SQRT(((SUM(Flowthru) + RetailNum)
...

Before casting anything, I would look for the origin of the problem.

3. Registered User
Join Date
Oct 2003
Location
Dallas
Posts
76

## do a case....

for example you have

select (SUM(Flowthru)/SUM(TotalCount)) * 100 AS CELCFtPct from table

select CELCFtPct = case when SUM(TotalCount)) = 0 then 0
else (SUM(Flowthru)/SUM(TotalCount)) * 100 end
from table

You can do that for all your divisions so you do not get division by zero error. Not sure what field you needed this on but its a good ideal to do this anytime that you are dividing because at some point you are bound to divide by zero.

Hope that helps

KG

4. Registered User
Join Date
Nov 2003
Posts
10

## Re: Division by Zero Error

Thank you for your help.. I figured out that my data types needed to be changed to float. I had it a varchar.

Joe

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•