Aggregate function SUM gives some junk value

Question

Aggregate function SUM gives some junk value

Santhoshkumar KB

SSCertifiable

Points: 5066
More actions
December 2, 2008 at 1:11 pm

#77144

Suppose there is a table TT with values
col (type float)
1212.26
2345.45
0
-2345.45
-1212.26
Now if I query this like
select SUM(col) from TT
then surprisingly the result was something like -2.7465348468E-13
(This was under SQL Server 2005)
when i queried the same thing in SQL Server 2000 Query Ananlyzer,
the result here also was something like -2.7465348468E-13
when i saw the table values
select * from TT
col
1212.26
2345.45
0
-2345.45
1212.259999999998
when i copied these values to EXCEL sheet, amazingly the (=SUM) was the same JUNK value.
Why is this happening?
The actual result must be equal to 0.0000000002 or almost 0.00,
but why am i getting junk value??
Any help will help me....
Thanks
Thanks,
Santhosh
Human Knowledge Belongs To The World !!

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

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

You're not getting a junk value, you're getting a value very close to 0. -2.7465348468E-13 is scientific notation. Expanded out it's 0.000000000000227373675443232

Float's aren't accurate data types. They're approximate and rounding errors are not uncommon. If you want complete accuracy to a specified number of decimal places, use numeric.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Adi Cohn-120898 SSC-Dedicated Points: 33944 More actions · Answer 2

Float is not an exact data type. If is an approximate data type. This means that it might have some mistakes but it should be few digits after the decimal point. This can explain why you get wrong results. By the way I tried to play a bit with the values that you showed, but I get the expected results (0).

Adi

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Santhoshkumar KB SSCertifiable Points: 5066 More actions · Answer 3

Thanks Gila,

Actually these types of result are affecting the production in one or other way.

some batches will be having a query like

WHERE col = '0'

in this case the rows with JUNK(float) value will be ignored....

If I use numeric data type then the precision must be declared right?

Any suggestion on how much precision value should be given?

(The value will be upto 8 or 10 digits)

Thanks,
Santhosh

Human Knowledge Belongs To The World !!

Santhoshkumar KB SSCertifiable Points: 5066 More actions · Answer 4

Also consider the values

1234.56

3456.67

45.67

78.34

0

-1234.56

-3456.67

-45.67

-78.34

The SUM of above must be equal to zero.

but at 1st execution it will show 0

next execution it will show -2.71243434E-11

next execution again -2.71243434E-11

next execution 0

like that.

So at the time when it fetches -2.71243434E-11, if it inserts to the table then it will be a problem.

Any Suggestion?

Thanks,
Santhosh

Human Knowledge Belongs To The World !!

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

santhoshkumar.boregowda (12/2/2008)
Thanks Gila,
Actually these types of result are affecting the production in one or other way.
some batches will be having a query like
WHERE col = '0'
in this case the rows with JUNK(float) value will be ignored....

Yes, they will. It's very risky to do that with float values. As Books Online says:

Avoid using float or real columns in WHERE clause search conditions, especially the = and <> operators. It is best to limit float and real columns to > or < comparisons.

If I use numeric data type then the precision must be declared right?
Any suggestion on how much precision value should be given?
(The value will be upto 8 or 10 digits)

You will have to state precision.

I can't suggest one. You need to determine how many decimal places are needed for your data.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

santhoshkumar.boregowda (12/2/2008)
The SUM of above must be equal to zero.

No way to ensure that with a float.

Any Suggestion?

Change the data type to one that is precise and accurate.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Sergiy SSC Guru Points: 110208 More actions · Answer 7

santhoshkumar.boregowda (12/2/2008)
Thanks Gila,
Actually these types of result are affecting the production in one or other way.
some batches will be having a query like
WHERE col = '0'
in this case the rows with JUNK(float) value will be ignored....
If I use numeric data type then the precision must be declared right?
Any suggestion on how much precision value should be given?
(The value will be upto 8 or 10 digits)

8 or 10 digits after decimal dot?

Or in total?

I giess money datatype with 4 decimal digits would be enough for any aggregated monetary value.

If it's true in your case then use this:

HAVING CONVERT(money, SUM(col) ) = 0

_____________
Code for TallyGenerator