sum function return type - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

sum function return type

Author

Topic

coolerbob
Aged Yak Warrior

841 Posts

Posted - 2008-11-07 : 10:30:07

Why does this:

declare @a real
set @a=3.33
select sum(@a)

return 3.32999992370605 and not 3.33?

I know I can call ROUND to bring it back to what it was, but why does it not already do it for me?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-07 : 10:32:43

since real and float are approximate data types. Try with decimal or numeric and you will get accurate figures

declare @a decimal(10,2),@b numeric(10,3)
select @a=3.33,@b=6.435
select sum(@a),sum(@b)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-07 : 10:32:55

Real and Float are approximate numbers only.
See Books Online.

Float and Real store a number in binary representation.
See here for more indepth explanation http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=81849

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-07 : 10:34:28

This is how FLOAT and REAL are stored

quote:


      SIGN BIT|<-----EXPONENT---->|<-----Mantisa---->>>> to bit 64 
             S|E E E E E E E E E E|M M M M M M M M M
 Power of 2  0|1 0 0 0 0 0 0 0 0 0|0 0 0 0 0 0 0 0 1 ... translates to 1/(2^(Power of 2))during calculation
             1|0 9 8 7 6 5 4 3 2 1|2 3 4 5 6 7 8 9 0 ... ETC
              |                   |
 BitNum      0|0 0 0 0 0 0 0 0 1 1|1 1 1 1 1 1 1 1 2
             1|2 3 4 5 6 7 8 9 0 1|2 3 4 5 6 7 8 9 0 ... etc


1 0011 1100 0111 1111 1111 1111 1111 1111 1111 1111 1111 1111 1111 1111 1111 1100  +2.77555756156289E-17
2 1011 1100 0111 1111 1111 1111 1111 1111 1111 1111 1111 1111 1111 1111 1110 1011  -2.77555756156288E-17
3 0011 1001 0111 0001 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000  +5.23852944873328E-32
..SEEE EEEE EEEE MANTISSA................................................MANTISSA

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources