2

I have table column that have positive and negative float digits. When I do query to get SUM() of all digits, it's returns wrong result.

Table:

+----+---------+--------+
| id | user_id | points |
+----+---------+--------+
|  1 |      38 | 30.5   |
|  2 |      38 | -5.3   |
+----+---------+--------+

Query:

SELECT SUM(points) FROM table WHERE user_id=38

Result:

25.199999809265137

Why is this happening? Why I don't get 25.2 as the result?

Molod
  • 352
  • 6
  • 22
  • is there any value after one place of point? – Vikas Hardia May 14 '14 at 12:46
  • could you post the table definition? If `points` is created as `DECIMAL(10,2)` everything works as expected. – vhu May 14 '14 at 12:47
  • @eggyal because of reason for this case. It is duplicate because of root of the problem. (I wasn't so sure about it's a dupe of that certain question - but.. suggest better alternatives) – Alma Do May 14 '14 at 12:47
  • This is what happens with floating point values. If this matters to you, then used fixed point types such as `decimal`. – Gordon Linoff May 14 '14 at 12:47
  • @AlmaDo: Having a similar answer **does not** make a question a duplicate. The test is "*is, from the questioner's perspective, this problem identical to that faced in the other question?*" – eggyal May 14 '14 at 12:49
  • @vhu: Now *that* is a duplicate! +1 – eggyal May 14 '14 at 12:50
  • Try the following: `SELECT ROUND(SUM(points), 1) AS Sum1Dec FROM MyTable WHERE user_id = 38` – Linger May 14 '14 at 12:58
  • sorry for the duplicate, couldn't found same question :( used DECIMAL as a type of column and it worked like a charm. – Molod May 14 '14 at 13:30

2 Answers2

4

As documented under Problems with Floating-Point Values:

Floating-point numbers sometimes cause confusion because they are approximate and not stored as exact values. A floating-point value as written in an SQL statement may not be the same as the value represented internally.

eggyal
  • 122,705
  • 18
  • 212
  • 237
1

I'm guessing your points field is a float, which is not exact.

"Approximate value" According to the mysql dcoumentation.

Use decimal or numeric types to make exact calcuations with a known amount of numbers behind the decimal point.

Or round the result of your query on 1 digit, because your sources are also 1 digit behind the decimal point. But know that it's rounded and exact comaprison is not possible (better: not 100% exact and predictable)

stUrb
  • 6,612
  • 8
  • 43
  • 71