Results 1 to 12 of 12
  1. #1
    Join Date
    Feb 2012
    Posts
    9

    Unanswered: Division arithmetic - -801 22012 - Division by 0

    Hi

    I am struggling to get a statement working and wondering how I can get around it:

    select (1 / (1/2)) from <table>;

    Where in reality my sql is actually similar to

    select (sum(capacity) / (count(deployments)/100)) as percentage from table;

    I've tried casting to decfloat, dec, real but can't seem to get past this.. ?

    Any ideas much appreciated!

    Many thanks

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    What is the result of
    Code:
    select count(deployments) from table
    I would do something like (untested code)

    Code:
    case count(deployments) 
    when 0
        then 0
    else 
        (sum(capacity) / (count(deployments)/100))
    end case
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  3. #3
    Join Date
    Feb 2012
    Posts
    9
    count(deployments) always returns a minimum of one.

    In my code I actually have :

    case when
    count(deployments) = 0
    then
    0
    else
    (
    case when
    sum(capacity) = 0
    then
    0
    else
    (sum(capacity) / (count(deployments)/100))
    end
    )
    end as percentage

    and this is where I was getting the division by 0 problem.

    So i've paired it back to the simplest part and came up with

    select (1 / (1/2)) from table
    as being able to cause an error.

    If i do

    select (1 /(1/1)) from table it works

    If i do it in another DB it works aswell:

    mysql> select (1 / (1/2));
    +-------------+
    | (1 / (1/2)) |
    +-------------+
    | 2.0000 |
    +-------------+
    1 row in set (0.02 sec)

    It looks like because the 1/2 returns a number smaller than 1, it is dropping the numbers after the decimal place and just substituting 0.

    Many thanks

  4. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    The arithmetic is INTEGER arithmetic. 1/2 using integers is 0. You need to convert each number to floating point before the division.

    Andy

  5. #5
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    You'll need to cast the division result [count(deployments)/100] to a non-integer datatype, otherwise it will get truncated to 0 if the result is less than 1. For example, [decimal(count(deployments))/100.]

  6. #6
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Try
    sum(capacity) / (NULLIF(count(deployments) , 0)/100.)
    or
    sum(capacity) * 100. / NULLIF(count(deployments) , 0)

    Note: Affix dot(".") to 100.

    Both would return NULL, if count(deployments) was 0.
    If you want 0 in that case, use COALESCE.
    COALESCE( sum(capacity) * 100. / NULLIF(count(deployments) , 0) , 0 )

  7. #7
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    If considered the precision of the result,
    it would be better to use
    sum(capacity) * 100. / NULLIF(count(deployments) , 0)
    rather than
    sum(capacity) / (NULLIF(count(deployments) , 0)/100.)

    For example:
    Code:
    ------------------------------ Commands Entered ------------------------------
    DESCRIBE
     VALUES 2 / ( NULLIF(10 , 0) /  100.);
    ------------------------------------------------------------------------------
    
     Column Information
    
     Number of columns: 1
    
     SQL type              Type length  Column name                     Name length
     --------------------  -----------  ------------------------------  -----------
     485   DECIMAL               31, 0  1                                         1
    
    
    ------------------------------ Commands Entered ------------------------------
    DESCRIBE
     VALUES 2 * 100. / NULLIF(10 , 0);
    ------------------------------------------------------------------------------
    
     Column Information
    
     Number of columns: 1
    
     SQL type              Type length  Column name                     Name length
     --------------------  -----------  ------------------------------  -----------
     485   DECIMAL               31,17  1                                         1
    Code:
    ------------------------------ Commands Entered ------------------------------
    VALUES 2 / ( NULLIF(10 , 0) /  100.);
    ------------------------------------------------------------------------------
    
    1                                
    ---------------------------------
                                  20.
    
      1 record(s) selected.
    
    
    ------------------------------ Commands Entered ------------------------------
    VALUES 2 * 100. / NULLIF(10 , 0);
    ------------------------------------------------------------------------------
    
    1                                
    ---------------------------------
                 20.00000000000000000
    
      1 record(s) selected.
    Last edited by tonkuma; 02-08-12 at 10:44. Reason: Add examples.

  8. #8
    Join Date
    Feb 2012
    Posts
    9
    many thanks to all.



    where i had tried :

    select double(1 / double(1/2)) from table

    and still received the division by zero i've now got it working as
    suggested with

    select double(1) / (double(1) / double(2)) from table;

    i.e. typecasting each number rather than the results.

    Many thanks

  9. #9
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Quote Originally Posted by niavasha View Post
    count(deployments) always returns a minimum of one.

    In my code I actually have :

    case when
    count(deployments) = 0
    then
    0
    else
    (
    case when
    sum(capacity) = 0
    then
    0
    else
    (sum(capacity) / (count(deployments)/100))
    end
    )
    end as percentage

    and this is where I was getting the division by 0 problem.
    If you tried my examples, all of the case expressions might be replaced by one of the expressions, like
    COALESCE( sum(capacity) * 100. / NULLIF(count(deployments) , 0) , 0 ) as percentage
    And, division by 0 problem would be avoided.
    Last edited by tonkuma; 02-08-12 at 11:24. Reason: Repeat one of the expressions.

  10. #10
    Join Date
    Feb 2012
    Posts
    9
    many thanks, I have now used your code in the end

  11. #11
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Although you might know and might use nested case expressions to avoid "Division by 0 problem".
    Generally speaking, unnecessary nested case expressions should be avoided.


    For example:

    case when
    count(deployments) = 0
    then
    0
    else
    (
    case when
    sum(capacity) = 0
    then
    0
    else
    (sum(capacity) / (count(deployments)/100))
    end
    )
    end as percentage
    Note: I used the code as an example of nested case expressions.
    (Actually, the case expressions might be avoided in your code, now.)

    To be generalized, it would be written like...
    Code:
           CASE
           WHEN <condition-1>
           THEN <expression-1>
           ELSE
                (CASE
                 WHEN <condition-2>
                 THEN <expression-2>
                 ELSE <expression-3>
                 END
                )
           END  AS column-name
    It should be written like the following to avoild unnecessary complexity.
    Code:
           CASE
           WHEN <condition-1>
           THEN <expression-1>
           WHEN <condition-2>
           THEN <expression-2>
           ELSE <expression-3>
           END  AS column-name
    or, format like
    Code:
           CASE
           WHEN <condition-1> THEN
                <expression-1>
           WHEN <condition-2> THEN
                <expression-2>
           ELSE <expression-3>
           END  AS column-name
    Rationale:
    CASE expressions allow an expression to be selected based on the evaluation of one or more conditions. In general, the value of the case-expression is the value of the result-expression following the first (leftmost) case that evaluates to true. If no case evaluates to true and the ELSE keyword is present then the result is the value of the result-expression or NULL. ...
    CASE expression - IBM DB2 9.7 for Linux, UNIX, and Windows

  12. #12
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    You could also use NULLIF():
    Code:
    SELECT ..., col1 / NULLIF(col2, 0)
    FROM ...
    That way, you don't have to write a CASE expression and you get a SQL NULL instead of a division-by-zero. Friendly arithmetics would also give you that if your version of DB2 supports this feature.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Posting Permissions

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