Avoid rounding off decimal data, when using SUM function

Question

Hi.

I have a column which is of type float.

I have a query which does the sum of that column. - select SUM(COLUMN1) AS CHEC0 FROM TableName

However, the resultant data of the decimal part is getting rounded off.

example: 2082124.755000

However, I am trying to get the output as : 2082124.75499996

How to avoid the rounding off data, when using SUM ?

Answer 1

However, the resultant data of the decimal part is getting rounded of

The SUM function don't round the result. The data type float is an approximate one and calculation can result in truncated decimals.
If you want to get a more presice result then use an exact data type like decimal.

Answer 2

What version of SQL Server are you using? Please post the results of SELECT @@VERSION.

First, float data type is an approximate value., not an exact value. You may never get that value depending on what you add too it.

Second, there was a bug which always returned the final digit as 0. This has been fixed. Please make sure you have the current patch level installed.

Answer 3

To analyse this in detail, we would need to see a repro that demonstrates the problem.

However, you are asking for a result with a precision of 15 digits. That is a lot. As a matter of fact, it's on the brink on what you can get with float. The precision of float is 15-16 decimal digits, or 53 binary ones. If you compute a sum from both positive and negative values, you can lose some of the precision.

It also strikes me as funny that you want this specific value. How you do that this exact value is correct? To arrive at this value you source data would have a precision which is beyond what SQL Server can handle in float. decimal may work, but that all depends on what your data is about.

But you could try running it in SQLCMD to see what you get. In the end, what you see is a character representation of the binary float value. SQLCMD and SSMS uses different libraries, and often produces a different display of floats.

Answer 4

Hi @Padmanabhan, Venkatesh ,

We recommend that you post your sample data and expected output so that we could reproduce your issue and avoid guessing.

You could also try with below:

select SUM(TRY_CONVERT(decimal(30,8), COLUMN1)) from TableName  
  
select SUM(TRY_CONVERT(decimal(28,10), COLUMN1)) from TableName  

Best regards,
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 5

a float column is in base 2, and can not accurately store decimal values. some decimal value are repeating values in base 2 (like 1/3 in base 10). so this means there is rounding when a decimal value is stored in a float and when a float is converted back to decimal.

if you sum floats you can lose precision. to understand lets do it in decimal and only allow a precision of 3

6.54 + 4.00 = 10.5 (only 3 digits precision, .04 precision lost)

as suggested you will get the most accurate sum if you convert the float to a precise enough decimal before summing. SQL Server decimal has a max precision of 38 digits. float has max precision of 16 digits (for special values). so to convert to decimal you need at least decimal(32,16). of course this will overflow if float > 1.0 e17, so y will need to max float value. it can possible to be larger than a decimal can hold.