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.
| Author |
Topic |
|
MorrisK
Yak Posting Veteran
83 Posts |
 Posted - 2012-07-14 : 10:24:27
|
| I've read several threads about how to divide integers but I still don't understand the following behavior.The following script divides 1/3 and returns 0.33333333333333 like I want.DECLARE @T TABLE( ID INT IDENTITY(1,1) ,ZERO_OR_ONE INT)INSERT INTO @T(ZERO_OR_ONE)VALUES(1)INSERT INTO @T(ZERO_OR_ONE)VALUES(0)INSERT INTO @T(ZERO_OR_ONE)VALUES(0)SELECT SUM(ZERO_OR_ONE) / (COUNT(ID) * 1.0) FROM @THowever, if I insert additional rows and change the calculation to 3/10 the result is rounded to 0.30000000000000.DECLARE @T TABLE( ID INT IDENTITY(1,1) ,ZERO_OR_ONE INT)INSERT INTO @T(ZERO_OR_ONE)VALUES(1)INSERT INTO @T(ZERO_OR_ONE)VALUES(0)INSERT INTO @T(ZERO_OR_ONE)VALUES(0)INSERT INTO @T(ZERO_OR_ONE)VALUES(1)INSERT INTO @T(ZERO_OR_ONE)VALUES(1)INSERT INTO @T(ZERO_OR_ONE)VALUES(0)INSERT INTO @T(ZERO_OR_ONE)VALUES(0)INSERT INTO @T(ZERO_OR_ONE)VALUES(0)INSERT INTO @T(ZERO_OR_ONE)VALUES(0)INSERT INTO @T(ZERO_OR_ONE)VALUES(0)SELECT SUM(ZERO_OR_ONE) / (COUNT(ID) * 1.0) FROM @TWhy does the second script return 0.30000000000000 instead of 0.33333333333333?Kevin |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-07-14 : 10:44:30
|
Three divided by 10 is really 0.3 rather than 0.33333. May be you mean that you want to ignore the columns that have zero values in the calculation? Or you really have the inverse in mind?SELECT (COUNT(ID) * 1.0 )/SUM(ZERO_OR_ONE) FROM @T |
 |
|
|
MorrisK
Yak Posting Veteran
83 Posts |
Posted - 2012-07-14 : 15:44:20
|
| Thanks sunitabeck. How embarrassing. No, that's what I really wanted. Looks like I need to go back to school though! |
|
|
|
|
|
|
Dividing integer columns