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
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
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.