0

Consider the query below. The Quantity column is of type REAL

SELECT Quantity
FROM foo
WHERE ID = 202224

enter image description here

However, doing a SUM results in many fractional digits (e.g. digits after decimal place):

SELECT SUM(Quantity)
FROM foo
WHERE ID = 202224

enter image description here

I can fix it by casting the result back to REAL:

SELECT CAST(SUM(Quantity) AS REAL)
FROM foo
WHERE ID = 202224

enter image description here

This feels like the wrong way to solve this problem. And it adds an extra Compute Scalar to the query plan.

Is there a better way to solve this problem?

AngryHacker
  • 59,598
  • 102
  • 325
  • 594
  • 5
    `REAL` is a floating-point data type. Performing arithmetic with floats will 99.9% of the time produce this result. I think the only *real* (pun completely intended) way to do this would probably be to cast to a `DECIMAL` before summing them because decimals don't do any approximation. – Jesse Oct 13 '22 at 22:47
  • 3
    In other words `SELECT SUM(CAST(Quantity AS decimal(38,19))...` See also [Is floating point math broken?](https://stackoverflow.com/questions/588004/is-floating-point-math-broken), no it isn't, you just have to understand the results. – Charlieface Oct 13 '22 at 23:09
  • 1
    Does this answer your question? [Is floating point math broken?](https://stackoverflow.com/questions/588004/is-floating-point-math-broken) – nbk Oct 13 '22 at 23:22

0 Answers0