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')
AND MONTH(FromDate) = MONTH(DATEADD(m,-2,GETDATE()))
AND YEAR(FROMDATE) = YEAR(DATEADD(m,-2,GETDATE()))
GROUP BY FromDate,State,Description,RetailNum,RetailDen

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.

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

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

Joe