Post reply

Divide by Zero

  • May 8, 2011 at 11:48 pm

    #235841

    I am getting a divide by zero error on the following statement that I have. I tried adding the isnull syntax to the statement but was not able to resolve the error. I know that a couple of rows where the sum(curamount) is zero.

    "cast(curamount/sum(curamount) over (partition by employeeid,docdate) as decimal (10,4)) as Total"

    I would appreciate any input to get over this error.

    Thanks

  • May 9, 2011 at 12:51 am

    #1321563

    Hi

    The run time error you are getting is mathematical issue.

    Any number different to 0 or infinite, when divided by 0 is infinite.

    As it is often said (although not 100% correct): "Division by 0 is not allowed"

    The answer to your question depends on what you want to return in case when sum(curamount) is 0?

    You might want to skip those records altogether or you need to show 0? Or something else?

    Could you please let us know what is needed in the resultset?

    Also, would be probably good idea to post the whole query, not just one line.

    Thanks.

  • May 9, 2011 at 6:23 am

    #1321670

    Can you replace those Nulls and Zeros to 1 before running the query?

    Or you can use a CASE WHEN to replace it in the query, but using CASE WHEN degrades the performance.

    _____________________________________________
    One ounce of practice is more important than tonnes of dreams

  • May 9, 2011 at 6:29 am

    #1321674

    "cast(curamount/ISNULL(NULLIF(sum(curamount), 0), 1) over (partition by employeeid,docdate) as decimal (10,4)) as Total"

  • May 9, 2011 at 4:45 pm

    #1322024

    Thank you for your response. I would like to show a zero when the divide by zero error is encountered.

    I know I can use the ISNull and ifnull but I am struggling as to where to place it in the syntax.

    Thanks

  • May 9, 2011 at 5:59 pm

    #1322042

    It seems to me that Ninja’s_RGR’us solution will not work.

    Tried to create something similar, but it reports the error:

    “Msg 4113, Level 15, State 1, Line 1

    "ISNULL" is not a valid windowing function, and cannot be used with the OVER clause.”

    I believe that this cannot be handled with NULLIF and ISNULL because if curamount values are both positive and negative, it is still possible to get sum=0 (over partition) and separate curamount values different to 0.

    For example there would be case

    -2/1

    2/1

    over same partition, while actually, it should be

    0 -2/(2+(-2))

    0 2/(2+(-2))

    My suggestion would be:

    Select case when SubTotal =0 then 0

    else CAST(curamount/SubTotal as decimal(10,4))

    end as Total from

    (

    select curamount,SUM(curamount) over (partition by employeeid,docdate) as SubTotal from TAbleName

    ) as X

    Hope that somoene will come up with something better, this is really interesting problem (to handle it in one line of code – couldn’t see the way how to do it)

  • May 9, 2011 at 7:44 pm

    #1322052

    An easy solution could be to do it in 2 steps (easier on the eyes too).

    Get all the sums > derived table

    ROW_NUMBER () OVER(col1 / case when col2 <> then col2 else 1 end) >> derived table again

    CASE WHEN col2 = 2 then 0 else Calculated_col3 end

    If that doesn't work I'll do a fully tested solution tommorrow. I'm sure the case idea works in the over(). I had never tried isnull(nullif)) in there b4. Sorry for the bad solution.

  • May 9, 2011 at 8:14 pm

    #1322059

    Seems that I managed to find a way to use CASE with OVER:

    select Case SUM(curamount over (partition by employeeid,docdate)

    when 0 then 0

    else curamount/SUM(curamount) over(partition by employeeid,docdate)

    end

    from TableName

    Thanks Ninja, your answer gave me the idea how to do it.

  • May 10, 2011 at 5:34 am

    #1322208

    Awesome, thanks for posting the final answer. Will surely help someone someday.

  • January 27, 2012 at 12:17 pm

    #1439571

    I'm the someone it surely helped today. Thanks! I had the concept right with the CASE but couldn't figure out exactly where it went with the OVER part.

    ~ J

  • January 29, 2012 at 6:55 am

    #1439732

    T-SQL incorrectly converts blanks to zero when they are cast as numerics. ARRRRGH! :hehe:

    select cast('' as numeric) some_column -- blank

    some_column

    ---------------------------------------

    Msg 8114, Level 16, State 5, Line 1

    Error converting data type varchar to numeric.

    select cast(' ' as numeric) some_column -- space

    some_column

    ---------------------------------------

    Msg 8114, Level 16, State 5, Line 1

    Error converting data type varchar to numeric.

    select cast('0' as numeric) some_column -- number

    some_column

    ---------------------------------------

    0

  • January 30, 2012 at 9:23 am

    #1440085

    Would this not work?

    CAST(ISNULL(curamount/NULLIF(SUM(curamount),0),0)

    OVER (PARTITION BY employeeid,docdate) AS decimal(10,4)) AS Total

    If SUM(curamount) is 0, return NULL, else return SUM(curamount). X / NULL is NULL (Same with +,-,*). If Result is NULL, return 0, else return Result.

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply