3

I am using SQL Server 2008, one of my requirements is to calculate population standard deviation. SQL Server provides a built-in function stdevp for the same. I am using it but I am befuddled by the result I am getting. Population standard deviation for a group of same numbers should be zero but here I get some different although minuscule value.

drop table guest.tempTable;

create table guest.tempTable (column1 varchar , column2 decimal(10,6))

insert into guest.tempTable values('a',3578.2700);
insert into guest.tempTable values('a',3578.2700);
insert into guest.tempTable values('a',3578.2700);        
insert into guest.tempTable values('a',3578.2700);

insert into guest.tempTable values('a',3578.2700);
commit;

The following is the stdevp query:

select stdevp(column2) from guest.tempTable
group by column1

The result that I get is 

------------------------ 
5.459150335692846E-5

which should have been 0.00.

The documentation for stdevp says that it returns a float. So I am guessing this must be a rounding off issue, but I am unable to find a solution.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
rirhs
  • 297
  • 2
  • 4
  • 9

1 Answers1

1

Floating point numbers are not 100% accurate.

The fact that floating-point numbers cannot precisely represent all real numbers, and that floating-point operations cannot precisely represent true arithmetic operations, leads to many surprising situations. This is related to the finite precision with which computers generally represent numbers.

For example, the non-representability of 0.1 and 0.01 (in binary) means that the result of attempting to square 0.1 is neither 0.01 nor the representable number closest to it. In 24-bit (single precision) representation, 0.1 (decimal) was given previously as e = −4; s = 110011001100110011001101, which is

0.100000001490116119384765625 exactly.

Squaring this number gives

0.010000000298023226097399174250313080847263336181640625 exactly.

Squaring it with single-precision floating-point hardware (with rounding) gives

0.010000000707805156707763671875 exactly.

But the representable number closest to 0.01 is

0.009999999776482582092285156250 exactly.

You can read about it HERE.

aF.
  • 64,980
  • 43
  • 135
  • 198
  • Thanks for the reply, yes this is a rounding off issue, but I was hoping if there was any solution anybody knew which would get the correct result, like if I change the precision of the said field(or the datatype itself) but I guess it would still be going through the same floating point arithmetic :(. – rirhs Jan 17 '12 at 09:53
  • 1
    One possible solution might be to do it without float. Use decimal or numeric. – aF. Jan 17 '12 at 09:56
  • I tried to use other data types but with no success, I am now relying on calculating stdev in the application layer instead of calculating it in the database layer, then I am rounding it off to a acceptable limit. Somehow stdev in java yeilds comparatively precise results (although they wont be still 0) as compared to SQLServer, hence I am taking this approach. – rirhs Jan 19 '12 at 10:19