locked
How To Avoid The “Divide By Zero” error in SQL??? RRS feed

  • Question

  • Hi,

    How To check Divide By Zero Error In this part Of Code?

    I am Using an SQL DataBase.

    Select

    cast(sum(isnull(col2,0)) *100/ sum(isnull(col1,0)) as varchar(50)) +' %'as[taux1] ,cast(sum(isnull(isnull(col3,0))*100 /sum(isnull(col1,0)) as varchar(50)) +' %' as [taux2] ,cast(sum(isnull(col4,0))*100/sum(isnull(col3,0)) asvarchar(50)) +' %' as [taux3] From dbo. Table1

    Thank you Very Much,

    Best Regards.



    • Edited by Bajtitou Friday, June 28, 2019 5:33 PM
    Friday, June 28, 2019 5:32 PM

Answers

  • You can use NULLIF  or CASE.

    NULLIF:

    SELECT @Value1/NULLIF(@Value2,0);

    CASE:

    SELECT CASE WHEN @Value2 = 0 THEN NULL
    ELSE @Value1/@Value2

    The return is NULL for this operations.

    • Marked as answer by Bajtitou Friday, June 28, 2019 6:48 PM
    Friday, June 28, 2019 6:13 PM

All replies

  • I would recommend using an inline if e.g.

    result = IIf(divisor = 0, 0, dividend) / IIf(divisor = 0, 1, divisor)

    See also https://www.techonthenet.com/access/queries/divide_by_zero.php


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange


    Friday, June 28, 2019 6:10 PM
  • You can use NULLIF  or CASE.

    NULLIF:

    SELECT @Value1/NULLIF(@Value2,0);

    CASE:

    SELECT CASE WHEN @Value2 = 0 THEN NULL
    ELSE @Value1/@Value2

    The return is NULL for this operations.

    • Marked as answer by Bajtitou Friday, June 28, 2019 6:48 PM
    Friday, June 28, 2019 6:13 PM
  • I would recommend using an inline if e.g.

    result = IIf(divisor = 0, 0, dividend) / IIf(divisor = 0, 1, divisor)

    See also https://www.techonthenet.com/access/queries/divide_by_zero.php

    Hi ,

    That is For Access Queries,

    I am Using Sql Database.

    Thank you very Much, Best Regards.

    Friday, June 28, 2019 6:47 PM
  • You can use NULLIF  or CASE.

    NULLIF:

    SELECT @Value1/NULLIF(@Value2,0);

    CASE:

    SELECT CASE WHEN @Value2 = 0 THEN NULL
    ELSE @Value1/@Value2

    The return is NULL for this operations.

    Hi, 

    Using NULLIF It is working Very Well.

    So Thank you very Much.

        Select 
            cast(sum(isnull(col2,0)) *100/ NULLIF(sum(isnull(col1,0)),0)  as varchar(50)) +' %'as[taux1]
            ,cast(sum(isnull(isnull(col3,0))*100 /NULLIF(sum(isnull(col1,0)),0)  as varchar(50)) +' %' as [taux2]
            ,cast(sum(isnull(col4,0))*100/NULLIF(sum(isnull(col3,0)),0) asvarchar(50)) +' %' as [taux3]
          From dbo. Table1

    Best Regards.

    Friday, June 28, 2019 6:53 PM