Division arithmetic - -801 22012 - Division by 0

niavasha · #1 (**permalink**) 02-08-12, 08:54

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

sathyaram_s · #2 (**permalink**) 02-08-12, 09:02

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

niavasha · #3 (**permalink**) 02-08-12, 09:10

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

ARWinner · #4 (**permalink**) 02-08-12, 09:27

The arithmetic is INTEGER arithmetic. 1/2 using integers is 0. You need to convert each number to floating point before the division.

Andy

n_i · #5 (**permalink**) 02-08-12, 09:29

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.]

tonkuma · #6 (**permalink**) 02-08-12, 09:29

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 )

tonkuma · #7 (**permalink**) 02-08-12, 09:40

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.

niavasha · #8 (**permalink**) 02-08-12, 10:02

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

tonkuma · #9 (**permalink**) 02-08-12, 10:18

Quote:

Originally Posted by niavasha

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.

niavasha · #10 (**permalink**) 02-08-12, 10:21

many thanks, I have now used your code in the end

tonkuma · #11 (**permalink**) 02-08-12, 20:17

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:

Quote:

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:

Quote:

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

stolze · #12 (**permalink**) 02-08-12, 22:25

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.

Internet Services

Web Development

Digital Marketing

Consumer Tech