0

I'm getting a bad result when adding up a column on DB2. The data is a string and I'm passing it through float() and then into sum().

The set looks like this:

 AMOUNT
 --------
 0.100   
 0.250   
 0.500   
 0.050   
 0.150   
 0.150   
 0.150   
 0.600   
 0.600   
 0.150

And when I add it I get the wrong result like so:

1>          select
2>              sum(float(amount)) as sum_amount
3>          from 
4>              TABLE
5>          where
6>              [CONDITIONS]
13> go
 SUM_AMOUNT
 ------------------
 2.6999999999999997

Any idea why this is happening? Suggestions to fix it?

CptAJ
  • 1,156
  • 1
  • 13
  • 20
  • 1
    I don't know what result you expected and why, but I think you should read this regardless: http://stackoverflow.com/questions/4664662/understanding-floating-point-problems – mustaccio Oct 16 '13 at 21:52

2 Answers2

3

Your answer is FLOAT because the input values are FLOAT.

Floating point numbers, and subsequently arithmetic, are approximate. If you want exact results, use the DECIMAL or NUMERIC data type with appropriate precision and scale instead of FLOAT.

WarrenT
  • 4,502
  • 19
  • 27
mustaccio
  • 18,234
  • 16
  • 48
  • 57
  • An additional option is to use `DECFLOAT`. But why use it, when `DEC` or `NUM` will get you exactly what you want, faster. – WarrenT Oct 16 '13 at 22:43
0

The short answer is all data is represented internally as binary and not decimal. Hence, fraction are "rounded" or more appropriately truncated in the binary format. That's why you are seeing what you are seeing.

Here's a wikipedia article if you want to read up. -> Floating point

Now do I discourage you from using float? If you are just summing and not planning to multiply or divide within the sum, I would suggest to cast it as decimal instead. Otherwise, continue casting it as float within the sum, but cast it back to decimal after everything is done. Specially if you are trying to compute a weighted average.

Robert Co
  • 1,715
  • 8
  • 14
  • The "all data is ...binary and not decimal" is misleading. Of course all modern memory chips store in bits. But in the DB2 (IBM) world at least, numbers are frequently encoded in base 10. `Numeric` is "zoned decimal" or [Binary Coded Decimal](http://en.wikipedia.org/wiki/Binary-coded_decimal#IBM_and_BCD). Each digit occupies the lower half of each byte, & the upper half of the last digit contains the sign, which was originally related to how punch cards represented data. `Decimal` is "packed decimal". Each digit occupies half a byte, with the last half byte reserved for the sign. – WarrenT Oct 16 '13 at 23:13
  • Agreed, the "all" is misleading. I am answering in the context of a floating point, which is encoded in mantissa x 10 ^ exponent. That's why the captain is seeing the anomaly in the first place. – Robert Co Oct 16 '13 at 23:21
  • I would tend to describe integers as binary values, but floating point is a data structure. – WarrenT Oct 16 '13 at 23:27
  • I am aiming to provide a "simplistic" explanation to what he is seeing. Hence, the link to wikipedia. Saying a float is a "structure" wouldn't clue him in as to why he is seeing all the numbers after the decimal point. – Robert Co Oct 16 '13 at 23:31
  • OK. No problem, just a difference of opinion / approach. IMO the average person learning computer programming should understand that binary generally refers to binary integers. If "structure" is confusing, then delving into the inner representation and computations of floating point is orders of magnitude more confusing. – WarrenT Oct 16 '13 at 23:45
  • Describing is as "structure" just obfuscate the topic, not necessarily confuse people. We want to encourage people to learn. By saying "it's complicated, just use this" is not helpful.I rather give people hints, especially if verbosity discourages them. – Robert Co Oct 17 '13 at 00:00