SUM() returns exponent based value instead of 0

Question

SUM() returns exponent based value instead of 0

AGersh

Old Hand

Points: 340
More actions
December 30, 2003 at 11:52 am

#161016

I'm trying to use the SUM function to get totals from a column. The column has 15 rows. The row consists of negative and positive numbers. When I sum the rows... I receive a high exponent based value (-7.27595761418343E-12) instead of (0).
Note: Keep in mind my field in SQL server is datatype (float) and length (8).

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

jxflagg Hall of Fame Points: 3817 More actions · Answer 1

I'm not sure what your question is. Are you saying that you want the sum to be rounded to an integer? You can use cast or convert:

select cast(sum(x) as int) from tablex

ThomasH SSCertifiable Points: 7437 More actions · Answer 2

Can you convert the FLOAT into a fixed DECIMAL without loosing information?

Select SUM( Convert(Decimal(10, 5), TheFloatCol) )....

Once you understand the BITs, all the pieces come together

AGersh Old Hand Points: 340 More actions · Answer 3

jxflagg,

I'm not looking to round the sum to an interger.

ThomasH,

Your suggestion is causing the following error "Arithmetic overflow error converting float to datatype numeric"

Here is sample data from one of the summarized columns. The SUM function should return (0) but instead it is returning a value of (-7.27595761418343E-12)

3500

-2364

-411

-243.04

-481.96

This SUM function/code that I am using for the above column/data "SUM(ISNULL(INDEMNITY_AMT, 0)) AS INDEMNITY_AMT"

Any ideas?

ThomasH SSCertifiable Points: 7437 More actions · Answer 4

What happens if you...

Select SUM( ROUND(TheFloatCol, 5) )....

Once you understand the BITs, all the pieces come together

AGersh Old Hand Points: 340 More actions · Answer 5

To correct the problem I have decided to change the columns DataType from Float(Length: 8, Precision: 53) to Decimal(Length: 9, Precision: 18, Scale: 2).

Any comments/notes regarding my decision?

noeld SSC Guru Points: 96590 More actions · Answer 6

quote:
This SUM function/code that I am using for the above column/data "SUM(ISNULL(INDEMNITY_AMT, 0)) AS INDEMNITY_AMT"
Any ideas?

SUM () by default DOES NOT ADD Null values

and in case you may not have records at all then do it the other way around:

Coalesce(SUM(INDEMNITY_AMT),0)

quote:
To correct the problem I have decided to change the columns DataType from Float(Length: 8, Precision: 53) to Decimal(Length: 9, Precision: 18, Scale: 2).
Any comments/notes regarding my decision?

For Precision Issues I have been able to get away with the MONEY type or smallmoney depending on the case!

HTH

Edited by - noeld on 12/30/2003 2:17:30 PM

* Noel

Frank Kalis SSC Guru Points: 111183 More actions · Answer 7

That's a common problem with float data. The float data type is imprecise and this cannot be changed. I think it is a relict from the stone age where storage space was expensive. Your decision to move to decimal was the best you can do IMHO.

Frank

http://www.insidesql.de

http://www.familienzirkus.de

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

mwelcome Right there with Babe Points: 739 More actions · Answer 8

When using floating point numbers. The underlying data is stored in a Base-2 binary format. Remember from back in your math classes that is is not always possible to exactly convert a faction to a decimal number.

For example 1/3 = .33333333... where the 3 repeats forever. That same problem occurs when converting a decimal number to base-2. For example 2.96 is 2 plus 96/100. This means the numbers that are being added already have conversion errors introduced even prior to the addition and rounding errors. Using the numeric data type prevents these conversion errors.