Post reply

Divide by zero error encountered.

  • July 13, 2012 at 7:17 am

    #258980

    Hello,

    I have some data and it contains value 0 on it. When I set up the formula for example, cogs/sales. Because there are some sales with 0 value so that the query I set up with formula does not work when you divide something with the 0. Do you know how to fix that problem?

  • July 13, 2012 at 7:19 am

    #1512519

    You would need to build a check in that anything which is 0 is set to 1 when doing the divide.

    Something like

    CASE WHEN col = 0 THEN 1 ELSE col END

  • July 13, 2012 at 7:19 am

    #1512520

    Try using NULLIF

    cogs/NULLIF(sales,0)

    ____________________________________________________

    Deja View - The strange feeling that somewhere, sometime you've optimised this query before

    How to get the best help on a forum

    http://www.sqlservercentral.com/articles/Best+Practices/61537

  • July 13, 2012 at 7:33 am

    #1512533

    Since two of the main methods have already been suggested I thought I'd introduce a third into the mix for completeness:

    SET ARITHABORT OFF

    SET ANSI_WARNINGS OFF

    SELECT ISNULL([Numerator] / [Denominator], 0)

    ==========================================================================================================================
    A computer lets you make more mistakes faster than any invention in human history - with the possible exceptions of handguns and tequila. Mitch Ratcliffe

  • July 13, 2012 at 8:01 am

    #1512552

    Thanks guys

Viewing 5 posts - 1 through 5 (of 5 total)

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