Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2003
    Posts
    10

    Wink 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')
    AND MONTH(FromDate) = MONTH(DATEADD(m,-2,GETDATE()))
    AND YEAR(FROMDATE) = YEAR(DATEADD(m,-2,GETDATE()))
    GROUP BY FromDate,State,Description,RetailNum,RetailDen
    Last edited by jgtorres; 11-09-03 at 04:08.
    --

    Joe Torres

  2. #2
    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. #3
    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. #4
    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
    --

    Joe Torres

Posting Permissions

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