none
Get Percentage from Two Columns

    Question

  • I am trying to get the a percentage from two columns, but I can not figure out how to do it. An example of my data is:

     

    NAME    MAX   ACT

    Red       1000    78

    Blue      1000    247

    Yellow   1000    472

     

    My thoughts was to do something like:

    SELECT  NAME, MAX, ACT, ((ACT / MAX)*100) as PERCENTAGE

    FROM TableName

     

    When I do this the PERCENTAGE column just has 0s.  Could someone tell me can this be done....and what am I missing.

     

    FYI:  Columns MAX and ACT are Int (4)

     

    Thanks!!

    Tuesday, February 12, 2008 11:20 AM

Answers

  • Your formula is correct, but when you do perform a mathematical operation between two INT variables, the result is always an INT. Hence the result of ACT/MAX is always 0.

     

    Here is how you can achieve the correct results.

     

    Code Snippet

    DECLARE @t TABLE (Name VARCHAR(10), maxValue INT, ActualValue INT)

    INSERT INTO @t (Name, MaxValue, ActualValue)

    SELECT 'RED', 1000, 78 UNION ALL

    SELECT 'BLUE', 1000, 247 UNION ALL

    SELECT 'YELLOW', 1000, 472

     

    SELECT

    Name,

    MaxValue,

    ActualValue,

    (((ActualValue * 1.00)/ maxValue) * 100) AS Percentage

    FROM @t

    /*

    Name       MaxValue    ActualValue Percentage

    ---------- ----------- ----------- ------------------------------

    RED        1000        78          7.8000000000000

    BLUE       1000        247         24.7000000000000

    YELLOW     1000        472         47.2000000000000

    */

     

     

    Note that I multiplied ActualValue with 1.0 which will result a float/decimal value.

    Another option is to do an explicit cast.

     

    Code Snippet

    SELECT

    Name,

    MaxValue,

    ActualValue,

    ((CAST(ActualValue AS DECIMAL(10,2))/ maxValue) * 100) AS Percentage

    FROM @t

     

     

    Tuesday, February 12, 2008 11:46 AM
    Moderator
  • This is most likely because of truncation involved with integer arithmetic.  Try instead

     

    (100*ACT)/MAX as PERCENTAGE

     

    This will still get truncated but won't cost you the whole answer.  Another alternative might be to do something like:

     

    (100.0*ACT)/MAX as PERCENTAGE

     

    in this way you can get decimal places.  You can then wrap a ROUND function around it if you want it rounded to a

    particular precision.

    Tuesday, February 12, 2008 11:48 AM
    Moderator

All replies

  • Your formula is correct, but when you do perform a mathematical operation between two INT variables, the result is always an INT. Hence the result of ACT/MAX is always 0.

     

    Here is how you can achieve the correct results.

     

    Code Snippet

    DECLARE @t TABLE (Name VARCHAR(10), maxValue INT, ActualValue INT)

    INSERT INTO @t (Name, MaxValue, ActualValue)

    SELECT 'RED', 1000, 78 UNION ALL

    SELECT 'BLUE', 1000, 247 UNION ALL

    SELECT 'YELLOW', 1000, 472

     

    SELECT

    Name,

    MaxValue,

    ActualValue,

    (((ActualValue * 1.00)/ maxValue) * 100) AS Percentage

    FROM @t

    /*

    Name       MaxValue    ActualValue Percentage

    ---------- ----------- ----------- ------------------------------

    RED        1000        78          7.8000000000000

    BLUE       1000        247         24.7000000000000

    YELLOW     1000        472         47.2000000000000

    */

     

     

    Note that I multiplied ActualValue with 1.0 which will result a float/decimal value.

    Another option is to do an explicit cast.

     

    Code Snippet

    SELECT

    Name,

    MaxValue,

    ActualValue,

    ((CAST(ActualValue AS DECIMAL(10,2))/ maxValue) * 100) AS Percentage

    FROM @t

     

     

    Tuesday, February 12, 2008 11:46 AM
    Moderator
  • This is most likely because of truncation involved with integer arithmetic.  Try instead

     

    (100*ACT)/MAX as PERCENTAGE

     

    This will still get truncated but won't cost you the whole answer.  Another alternative might be to do something like:

     

    (100.0*ACT)/MAX as PERCENTAGE

     

    in this way you can get decimal places.  You can then wrap a ROUND function around it if you want it rounded to a

    particular precision.

    Tuesday, February 12, 2008 11:48 AM
    Moderator
  • I want to say thank you to both of you for responding.  I tried the change that "Kent Waldrop Fe08" recommended, just because it is less of a change  and I am LAZY!!  It works great.

     

    Thanks again guys!!

    Tuesday, February 12, 2008 11:40 PM