Results 1 to 10 of 10
  1. #1
    Join Date
    Oct 2011
    Posts
    11

    Unanswered: Percentage calculation

    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?

    Thank you in advance!

    Tom.

  2. #2
    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. #3
    Join Date
    Oct 2011
    Posts
    11
    Quote Originally Posted by JarlH View Post
    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. #4
    Join Date
    Dec 2008
    Location
    At work...
    Posts
    92
    Quote Originally Posted by tinyevil View Post
    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. #5
    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. #6
    Join Date
    Oct 2011
    Posts
    11
    Quote Originally Posted by tonkuma View Post
    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. #7
    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.".

    Quote Originally Posted by tonkuma View Post
    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. #8
    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.

    Thanks for your help!

    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. #9
    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. #10
    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
  •