Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2002
    Location
    Omaha, NE
    Posts
    25

    Unanswered: BIGINT to DECIMAL and Math

    I have a few BIGINT columns I would like to add and then divide producing a percentage. When I do this in SQL, I receive a result of 0. When I do it with a calculator, it comes out to .97 or something. I am certain I am selecting only one record.

    This returns 0

    select
    a / (a + b) as foo
    from
    bar

    This returns 0.000

    select
    decimal(a / (a + b), 4, 3) as foo
    from
    bar

    Thank you for your assistance.

  2. #2
    Join Date
    Nov 2002
    Location
    Omaha, NE
    Posts
    25

    Smile

    I knew this was something a little "Infected Mushroom" could help clear up

    select
    decimal(float(a) / (float(a) + float(b)), 4,3)
    from
    foo

    returns .997

  3. #3
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Bigints do integer calculus:

    3/3 = 1
    6/3 = 2
    5/3 = 2 the rest is lost
    a bigint can't represent 0.99, only discrete values like 1 or 0
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  4. #4
    Join Date
    Dec 2005
    Posts
    273
    simply convert one of the arguments ( not the result ! ) to DECIMAL:

    SELECT
    DECIMAL(a) / (a+b)
    FROM
    bar

  5. #5
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Quote Originally Posted by Wim
    Bigints do integer calculus:

    3/3 = 1
    6/3 = 2
    5/3 = 2 the rest is lost
    a bigint can't represent 0.99, only discrete values like 1 or 0
    Actuall, integer calculus means that 5/3 = 1. First you divide and then truncate fractions. There is no rounding involved.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  6. #6
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Actuall, integer calculus means that 5/3 = 1.
    Indeed, my error.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •