CODEWITHSUNDEEP
plsqloracle11g
user2751691
user2751691

Reputation: 411

Oracle sum function precision issue while calculating a column of values

In Pl/Sql, I use query string

SELECT company,
       SUM (holding_balance) AS total
FROM Atable
GROUP BY company

holding balance
0
0
-9173992.75
8986565.87
458448.13
0
-271021.25
0
-0.01
0.01
-191459114.33
514040.08
191459114.33
1390695.05
0
-351838.55
0
0.01
-1904735.13
-5757.71
357596.25
0
-124435850.66
144710230.66
-21629220.86
0
4966852.93
4563987.5
-8175999.57
0

The query returns 0 as total. However, when i copied and pasted the data into Excel and used sum, it returns 1.49012E-08.

I want my query to return 1.49012E-08 as well. How do you achieve/modify the above query? Thanks a lot!

Upvotes: 0

Views: 3065

Answers (1)

Justin Cave
Justin Cave

Reputation: 231791

Excel is incorrect. Oracle is correct. Are you really sure that you want the incorrect result?

Logically, the data that you've posted has no more than two digits of decimal precision. Adding and subtracting numbers with no more than two digits of decimal precision, the correct result cannot possibly have more than two digits of decimal precision. A sum of 1.49012E-08 (0.0000000149012) does not make sense. Think of a calculation in terms of dollars and cents. If you add and subtract payments to and from a checking account that are in terms of dollars and cents, you're never going to end up with a balance that is a small fraction of a penny. The Excel result is due to the fact that it is trying to use floating point numbers in which case its calculations will inevitably accumulate rounding errors.

If you really want to force Oracle to compute a result that accumulates rounding errors, you could try casting the data to float or binary_float data types before doing the sum. It's very unlikely that you'd get exactly the same rounding error that Excel gets but it is reasonably likely that you'll get an incorrect non-zero result that reflects rounding errors introduced in Oracle's calculations. It seems really unlikely, though, that you want an imprecise result.

Upvotes: 2

Related Questions