# Thread: divide by zero and decode statement

1. Registered User
Join Date
Sep 2003
Posts
9

## Unanswered: divide by zero and decode statement

I am calculating a weighted average number from TABLE (the
complete select is listed at the bottom).

The actual weighted average formula is:

sum((e / d) * (a + b + c)) / sum((a + b + c))

To avoid divide by zero errors, I use the decode statement
to check the devisors for NULL and 0. First I check "sum((a + b + c))".
If <> NULL and <> 0 then I check "sum(d)". If <> NULL and
<> 0 then I execute the above formula.

When column d for all records is NULL, the select executes
fine, returning -3. When column d for all records is 0, the
select aborts (ORA-01476: divisor is equal to zero), I expected
to have -4 returned.

Any ideas or better ways to avoid divide by zero?

select decode(sum((a + b + c)),NULL,-1,0,-2, decode(sum(d),NULL,-3,0,-4, sum((e / d) * (a + b + c)) / sum((a + b + c))))
from TABLE

2. Registered User
Join Date
Jul 2003
Posts
2,296
I use REPLACE if I might get a zero.
Can you replace the zero with 1 or will that screw up your formula?
REPLACE(d,0,1)

Otherwise you could use a CASE statement to check the value of 'd' before you ran the formula.

I am also wondering why you use SUM at all.
Wouldn't you get the same result with:
PHP Code:
``` ((e / d) * (a + b + c)) / (a + b + c)  ```

3. Registered User
Join Date
Sep 2003
Posts
9
Duck,

Thanks for the reply. I use the sum because I'm actually grouping
a lot of records in the table to return the single number. I omitted
the grouping logic from my post. My bad, I should have explained the
select a little better. Sorry.

Can I use a replace with a #, I thought it was just a string
function (I'm still on 7.3).

I've got a stored procedure that is simply a select statement that
tries to summarize a range of records down to a weighted avg. I'm
trying to do it in just a single select, am I asking to much of sql?

Thanks again for your input and time. I appreciate it.

Jim

#### Posting Permissions

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