1. Registered User
Join Date
Oct 2011
Posts
11

Hello.

After a few hours of tearing my hair out, i'm afraid i have to ask for some of your valuable time in helping me...

I have a table with 3 columns; Site, Live, Target. I have a stored procedure which creates a temporary table and totals the data. Please see below for an example:

Code:
```Site    Live    Target
0LO      2          2
1BL      1          5
2XA      3          4
3PT      0          0
4FH      0          2
5KL      1          0```
What i am TRYING to get is...

Code:
```Site    Live    Target    Conversion
0LO      2          2           100%
1BL      1          5            20%
2XA      3          4           75%
3PT      0          0           ??%
4FH      0          2            0%
5KL      1          0           ??%```
What seems like a simple [Live]/[Target]*100 calculation is proving to much for me as i'm having to take into account values of '0'. When i use this simple calculation, it returns a 'cannot divide by zero' error. Very frustrating.

Additionall, for sites 3PT and 5KL, i'm actually not sure what conversion % is mathematically and logically correct... My head is frazzled!

Can someone save me?

Tom.

2. Registered User
Join Date
Dec 2008
Location
At work...
Posts
92
Put the calculation in a case-expression.

CASE WHEN Target <> 0 then ...
ELSE ... END

EDIT: When Target is 0, I'd like to return NULL. (And then you can skip the ELSE part of the CASE.)

3. Registered User
Join Date
Oct 2011
Posts
11
Originally Posted by JarlH
Put the calculation in a case-expression.

CASE WHEN Target <> 0 then ...
ELSE ... END
Hello.

I tried using the below, but it set all the Conversion values to '0'.

Code:
```UPDATE REPConversion SET Conversion = CASE WHEN (GoneLive <> 0 AND Target <> 0)
THEN [GoneLive]/[Target]*100
ELSE 0
END```

4. Registered User
Join Date
Dec 2008
Location
At work...
Posts
92
Originally Posted by tinyevil
Hello.

I tried using the below, but it set all the Conversion values to '0'.

Code:
```UPDATE REPConversion SET Conversion = CASE WHEN (GoneLive <> 0 AND Target <> 0)
THEN [GoneLive]/[Target]*100
ELSE 0
END```
If you are using integer data types for GoneLive and Target, the calculation will be done "integer wise", i.e. only the integer part of the division is returned (which is 0 in your cases.)

To avoid this behavior, multiply [GoneLive] with 1.0 and your calculation will be done as you expect. (Or adjust 100 to 100.0, or start with 100 *)

5. Registered User
Join Date
Feb 2008
Location
Japan
Posts
3,483
It may be slightly different on each RDBMS.

In DB2, use the expression
COALESCE( Live * 100. / NULLIF(Target , 0) , 0 )
(I think this is an idiomatic expression to calculate percentage for INTEGERs on DB2.)

Divide by zero is not possible(not defined)
for mathematical "field" algebraic system(i.e. system on which addition/subtraction/multiplication/division were difined).

So, I thought to return NULL or 0 for divided by 0 would be practical.
Return NULL: Live * 100. / NULLIF(Target , 0)
Return 0: COALESCE( Live * 100. / NULLIF(Target , 0) , 0 )
Last edited by tonkuma; 02-14-12 at 12:31. Reason: Add NULL for divide by 0

6. Registered User
Join Date
Oct 2011
Posts
11
Originally Posted by tonkuma
It may be slightly different on each RDBMS.

In DB2, use the expression
COALESCE( Live * 100. / NULLIF(Target , 0) , 0 )
(I think this is an idiomatic expression to calculate percentage for INTEGERs on DB2.)

Divide by zero is not possible(not defined)
for mathematical "field" algebraic system(i.e. system on which addition/subtraction/multiplication/division were difined).

So, I thought to return NULL or 0 for divided by 0 would be practical.
Return NULL: Live * 100. / NULLIF(Target , 0)
Return 0: COALESCE( Live * 100. / NULLIF(Target , 0) , 0 )
Spot on, that did just the trick! I had a slight issue with my update query, where it looked like it was recording the wrong value. However this was a rounding issue, i've now changed the 'Conversion' field to a decimal, re-ran the update and all is i would expect.

Thanks for your help! Much appreciated!

7. Registered User
Join Date
Feb 2008
Location
Japan
Posts
3,483
Here is an example comparing to multiply dividend by "100." and to divide divisor by "100.".

Originally Posted by tonkuma
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.```

8. Registered User
Join Date
Oct 2011
Posts
11
To me, accuracy of data calculation is the most important thing. If a calculation is 5.88%, i need THAT to be displayed rather than 6%.

Therefore, "sum(capacity) * 100. / NULLIF(count(deployments) , 0)" will be in my script.

I will do some research myself, but could you give me your understanding on what "COALESCE" does as a function? I've never come across it before, and after trying lots of different methods to resolve this issue, it was the golden ticket.

9. Registered User
Join Date
Dec 2008
Location
At work...
Posts
92
COALESCE(x1,x2,...,xn)

is equivalent to:

CASE WHEN x1 IS NOT NULL THEN x1
ELSE COALESCE(x2,...,xn) END

I.e. the COALESCE expression returns the value of the first non-NULL operand, found by working from left to right, or NULL if all the operands equal NULL.

10. Registered User
Join Date
Oct 2011
Posts
11
Thanks JarlH!

#### Posting Permissions

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