Results 1 to 5 of 5
  1. #1
    Join Date
    May 2010
    Posts
    52

    Unanswered: SQL SERVER SUM Function gone wild.

    So i have 4 fields float as the data type. one of the 4 fields is giving me a wild result when i sum the colulm. i have these values in the column
    -69.66
    null
    -474.51
    null
    474.51
    69.66.
    sum(column) is giving me this value 2.8421709430404E-14. what is SQL doing.

    the other columns have basically the same format of data and i am getting the correct result.
    Someone help me.

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    It's because of the float data type. It is not an exact data type. If the column were defined as numeric (10, 3), or something similar, you should see your data sum up to 0.

  3. #3
    Join Date
    May 2010
    Posts
    52
    but how come the other columns that have the similar data type (float) are giving me correct results? puzzled?

  4. #4
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    If they don't have similar data, or they sum to a result further away from 0 (the sum has more significant digits), then the small rounding errors will be less likely to surface.

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    This is a fundamental problem with Floating Point numbers. It is inherant in the nature of what they are and how they are stored.

    See:

    The trouble with rounding floating point numbers ? The Register
    Rounding And Precision In Excel

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Posting Permissions

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