Post reply

get a percentage

  • March 14, 2007 at 7:08 am

    #171630

    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

    #694924

    no i don't think there is.

    you create your own UDF for that.

    Everything you can imagine is real.

  • March 14, 2007 at 7:21 am

    #694927

    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

    #694925

    No need for a UDF :

    SELECT (col1 * 1.0/col2 * 100) AS percentage FROM table1

  • March 14, 2007 at 7:27 am

    #694931

    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

    #694935

    I can see the confusion .

  • March 14, 2007 at 7:41 am

    #694941

    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

    #694946

    1.0 : converts to a decimal

    *100 : so it shows 65.98 instead of 0.6598

  • March 14, 2007 at 7:49 am

    #694948

    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

    #694951

    Thanks everyone!

  • March 15, 2007 at 10:31 am

    #695285

    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

    #695289

    by multiplying one side by .01 you are moving the decimal place over.  The usual way to do this is to multiply the result of the whole operation by 100.  because you are using the count it is a numeric result not a varchar

    Either way works

    (10/100)*100 =10

    (10/(100*.01)) = 10

     

     

  • March 15, 2007 at 11:03 am

    #695294

    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

    #695297

    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

    #695303

    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