# 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

• 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
• 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

### 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
• 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
• 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