locked
Divide by zero error encountered RRS feed

  • Question

  • Hello, I have a line of script that occasionally  returns a 'divide by zero error':

    CAST SUM(a.mydata1) / CAST(SUM(a.mydata2) AS NUMERIC(10,2)) AS NUMERIC (10,0)) AS [AVERAGE TIME]

    Is there anything I can do here to amend this to bring through NULL - or 0 - in the instances when the above script is trying to calculate 0 / 0  - which it sometimes does?

    Any help appreciated - thanks

    Thursday, August 29, 2019 7:15 AM

Answers

  • Try this:

     

    CAST(SUM(a.mydata1) / CAST(NULLIF(SUM(a.mydata2), 0) AS NUMERIC(10,2)) AS NUMERIC (10,0)) AS [AVERAGE TIME]

     

    and this:

    CAST(ISNULL(SUM(a.mydata1) / CAST(NULLIF(SUM(a.mydata2), 0) AS NUMERIC(10,2)), 0) AS NUMERIC (10,0)) AS [AVERAGE TIME]

    Thursday, August 29, 2019 7:25 AM
  • CREATE TABLE tbl (test INTEGER,test1  INTEGER )
    INSERT INTO tbl VALUES (1,0)

    select (SUM(test) * 100.0)/ CASE WHEN SUM(test) = 0 
                                   THEN 1.0 ELSE sum(test) END

       from tbl


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Thursday, August 29, 2019 7:32 AM
    Answerer

All replies

  • Hello, I have a line of script that occasionally  returns a 'divide by zero error':

    CAST SUM(a.mydata1) / CAST(SUM(a.mydata2) AS NUMERIC(10,2)) AS NUMERIC (10,0)) AS [AVERAGE TIME]

    Is there anything I can do here to amend this to bring through NULL - or 0 - in the instances when the above script is trying to calculate 0 / 0  - which it sometimes does?

    Any help appreciated - thanks

    Thursday, August 29, 2019 7:02 AM
  • Try this:

     

    CAST(SUM(a.mydata1) / CAST(NULLIF(SUM(a.mydata2), 0) AS NUMERIC(10,2)) AS NUMERIC (10,0)) AS [AVERAGE TIME]

     

    and this:

    CAST(ISNULL(SUM(a.mydata1) / CAST(NULLIF(SUM(a.mydata2), 0) AS NUMERIC(10,2)), 0) AS NUMERIC (10,0)) AS [AVERAGE TIME]

    Thursday, August 29, 2019 7:25 AM
  • CREATE TABLE tbl (test INTEGER,test1  INTEGER )
    INSERT INTO tbl VALUES (1,0)

    select (SUM(test) * 100.0)/ CASE WHEN SUM(test) = 0 
                                   THEN 1.0 ELSE sum(test) END

       from tbl


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Thursday, August 29, 2019 7:32 AM
    Answerer
  • apologies - happy for this to be deleted - it seemed more a SQL query than SSRS but too late.
    Thursday, August 29, 2019 7:38 AM
  • Hi 

    Try change  from SUM(a.mydata2)  to iif(SUM(isnull(a.mydata2,0))=0,1,SUM(isnull(a.mydata2,0)))

    I hope this solution help you. 

    Best Regards,

    Natig


    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. 


    Thursday, August 29, 2019 8:21 AM
  • Hi,

    Please try following script .

    case when CAST(SUM(a.mydata2) AS NUMERIC(10,2))= 0 
    then 0 
    else CAST( SUM(a.mydata1) / CAST(SUM(a.mydata2) AS NUMERIC(10,2)) AS NUMERIC (10,0)) end  AS [AVERAGE TIME]

    Best Regards,

    Rachel


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, August 29, 2019 8:26 AM
  • Thank you for the very prompt response: worked perfectly and taught me something about NULLIF
    Thursday, August 29, 2019 1:12 PM
  • Thank you for the very prompt response, which worked and was perfect for my needs (and taught me a bit)
    Thursday, August 29, 2019 1:13 PM
  • Thanks for getting back
    Thursday, August 29, 2019 1:14 PM