Post reply

Division always returns 0 while trying to calculate a percentage.

  • September 29, 2009 at 11:48 am

    #385460

    I have a weird feeling that this is going to be pretty obvious. But I'm having problems calculating percentages with MS SQL. Perhaps I'm using a wrong datatype. I've tried using float, numeric and decimal and I get the same results every time.

    If I do this I get 0.50 which is the expected result:

    select CAST('.5' as numeric(3,2))

    But for some reason if I do the same thing but instead of putting in .5 I put in a calculation I always get back 0:

    select CAST((1/2) as numeric(3,2))

    I've even tried to take the 1/2 out and put it into a variable:

    DECLARE @numb numeric(3,2)

    SET @numb = 1/2

    print @numb

    However no matter what I do I always get back 0.

    Any help with this little blockage would be very much appreciated. I have no idea what I'm doing wrong and after a few searches I was unable to find exactly what I needed.

  • September 29, 2009 at 11:52 am

    #1059646

    When you divide an integer by an integer, you get an integer. CIP...

    select CAST((1/2) as numeric(3,2))

    select CAST((1/2.0) as numeric(3,2))

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • September 29, 2009 at 11:52 am

    #1059647

    When you divide 1 by 2 (1/2) you are doing integer math, so the remainder is dropped. Cast the values to numeric first if you want numeric results.

    Example:

    select A = 1/2 , B = 1.0/2.0

    Results:

    A B

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

    0 .500000

  • September 29, 2009 at 11:57 am

    #1059649

    Ahhhh!!! that makes sense. Thanks guys. Need more coffee when trying to problem solve 🙂

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

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