March 14, 2007 at 7:08 am
Is there a way in SQL to get the percentage of 2 columns? I tried using
SELECT (col1/col2) AS percentage FROM table1
But that does not work.
Are there any built-in functions to do this?
Thanks
March 14, 2007 at 7:20 am
March 14, 2007 at 7:21 am
If the two values are integers, it won't work because SQL returns a result of the same datatype as the data it's dividing. If you want a decimal percentage, you'll need to cast one or both of the integers into decimals as follows:
SELECT (CAST( col1 AS DECIMAL ) / col2) AS percentage FROM table1
March 14, 2007 at 7:21 am
No need for a UDF :
SELECT (col1 * 1.0/col2 * 100) AS percentage FROM table1
March 14, 2007 at 7:27 am
Ninja, that's a really awesome solution. Better not to clutter everything up with CASTs. One thing though... If you want the percentage in a whole number format, you'll have to multiply col2 by .01, not 100...
SELECT (col1 * 1.0)/(col2 * 0.01) AS percentage FROM table1
Never mind the above. I saw this as (col1 *1.0)/(col2 * 100) for some reason.
March 14, 2007 at 7:34 am
I can see the confusion .
March 14, 2007 at 7:41 am
SELECT (col1 * 1.0/col2 * 100) AS percentage FROM table1
why do you have to multiply one column by 1.0 and the other by 100?
Thanks!
March 14, 2007 at 7:47 am
1.0 : converts to a decimal
*100 : so it shows 65.98 instead of 0.6598
March 14, 2007 at 7:49 am
you are multiplying the first column by 1.0 to ensure that a decimal is returned if col1 is an int and you are multiplying the results of (col1 * 1.0 / col2) * 100 to change your decimal to a percentage. multiplication and division have the same precedent so your operations go from left to right. It may be easier to see as
((col1 * 1.0) / (col2)) * 100
March 14, 2007 at 7:56 am
Thanks everyone!
March 15, 2007 at 10:31 am
so why did I have to use the following in order to get my percentage to be accurate with decimals?
(COUNT (AREA))/(COUNT(UNITID)*.01)) AREA_USAGE,
area is varchar, unitid is varchar
March 15, 2007 at 10:49 am
March 15, 2007 at 11:03 am
mrpolecat -
I have tried this about 8 times moving things around but every way I position the *100 I get a result of 0
do me a big favor and put the *100 where it should be to give the result.
thanks very much.
March 15, 2007 at 11:16 am
The reason you are not getting the desired result is because the count is returning int and the redult returned is therefor an int with no decimal places. The way you have it the the denominator is being multiplied by a decimal and the result is converted to a decimal. Somewhere along the line this conversion must take place.
select convert(decimal,count(AREA)) / convert(decimal,count(UNITID)) * 100 AREAUSAGE
should work. Although since what you are doing is working why change it?
March 15, 2007 at 11:25 am
mrpolecat -
Thanks for the informative response -
I can't troubleshoot if I don't understand what I'm doing. It was so simple it would have taken me a long time to figure it out - thanks for the wake up! It was an easier solution that adding convert and now I'm confident to use it again.
Thanks for the help.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply