CODEWITHSUNDEEP
mysqlfloating-pointsumdecimal
Jayesh Babu
Jayesh Babu

Reputation: 1449

why does sum() query returns result with more decimal points?

enter image description here

The above image shows one of the columns named 'igstAmt' in my database table. when i use the query "SELECT sum(igstAmt) as igstAmt FROM salesinvoice, it returns the value 21616.7500129491 with many decimal points, but the correct answer is 21616.75. I know I can round up the result while displaying, I want to know why is this happening like this? The maximum number of decimal points in the entries in that column is two, so the result also should have 2 decimal points, right? The datatype of the column is float.

Upvotes: 5

Views: 5087

Answers (1)

Eric Postpischil
Eric Postpischil

Reputation: 222938

The float data type does not represent numbers with decimal digits. It represents numbers with binary, so the binary digits in the numbers represent powers of two such as 16, 8, 4, 2, 1, ½, ¼, ⅛, and so on.

The numbers shown as “272.7”, “0.55”, and so on are not accurate representations of the stored values. For example, the number shown as “272.7” may actually be 272.70001220703125.

The rules used to format numbers for output may hide these differences from you by using fewer than all the digits needed to show the exact value.

When you add these numbers, the differences between the actual numbers and the nearby decimal representations may grow or shrink, depending on chance variations between the differences. The sum you are seeing is a natural result of these additions.

Upvotes: 2

Related Questions