# Thread: Division arithmetic - -801 22012 - Division by 0

1. Registered User
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. Super Moderator
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```

3. Registered User
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. Registered User
Join Date
Jan 2003
Posts
4,310
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. :-)
Join Date
Jun 2003
Location
Posts
5,516
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. Registered User
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. Registered User
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. Registered User
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. Registered User
Join Date
Feb 2008
Location
Japan
Posts
3,483
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.
Last edited by tonkuma; 02-08-12 at 11:24. Reason: Repeat one of the expressions.

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

11. Registered User
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. Registered User
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.

#### Posting Permissions

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