SUM(Real) producing wierd results

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

SUM(Real) producing wierd results

Author

Topic

FruitBatInShades
Yak Posting Veteran

51 Posts

Posted - 2008-07-10 : 11:07:52

I am summing some real fields (just 2 point precision numbers 0.00) but SUM is returning some wierd numbers

SUM(0,0,2.95) is returning 2.95000004768372. I know I can round back to 2 point but am a bit concerned about the validity of my results!

Does anyone know how to hold small precision numbers and sum them accurately?

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-07-10 : 11:45:27

Use a precise type like NUMERIC or DECIMAL (or MONEY if these are money values). FLOAT / REAL are approximate types.

DECIMAL works with (WIDTH, PRECISION) SO a DECIMAL 6,2 is 6 digits wide with 2 decimals of precisions meaning that it can hold a range from 9999.99 to -9999.99
try


DECLARE @sumTable TABLE (
	[field1] DECIMAL (6,2)
	)

INSERT INTO @sumTable
      SELECT 0
UNION SELECT 0
UNION SELECT 2.95

SELECT SUM([field1]) FROM @sumTable

-------------
Charlie

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-07-10 : 11:53:07

Compare the two selects here...


DECLARE @sumTable TABLE (
	[field1] DECIMAL (6,2)
	)

INSERT INTO @sumTable
      SELECT 0
UNION SELECT 0
UNION SELECT 2.95

SELECT SUM([field1]) FROM @sumTable

DECLARE @sumTableb TABLE (
	[field1] REAL
	)

INSERT INTO @sumTableb
      SELECT 0
UNION SELECT 0
UNION SELECT 2.95

SELECT SUM([field1]) FROM @sumTableb

-------------
Charlie

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources