Post reply

My Percentage calculation is bringing out 0's and 1's

  • February 5, 2008 at 7:13 am

    #203001

    Help! im new to this sql stuff. Im try to do a "Simple" percentage calculation by dividing column x by column y, the results are 0's and 1's (1's when column x and y are the same) oh dear im sure it shouldn't be this hard!:crazy:

  • February 5, 2008 at 7:39 am

    #775124

    The default behavior with division on SQL Server is to return the SAME datatype as the starting columns. So if ColA is an integer, the result of ColA/5 would be expressed as an integer.

    In your case, that means only 0 or 1 can be returned, since it's going to truncate everything after the decimal point.

    Solution: Try CASTING your integer numerator to a float or decimal first.

    So -

    Select Cast(colA as decimal(10,6))/colB --should return a decimal result

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

  • February 5, 2008 at 7:43 am

    #775128

    select t1 = 10/100, t2 = 100/100, t3 = 10./100., t4 = 100./100.

    Results:

    t1 t2 t3 t4

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

    0 1 .100000 1.000000

    (1 row(s) affected)

  • February 5, 2008 at 8:04 am

    #775139

    Hi, THATS BRILL! ive crackt it, thanks very much.

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply