var function results - 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

General SQL Server Forums

New to SQL Server Programming

var function results

Author

Topic

malg
Starting Member

3 Posts

Posted - 2006-05-10 : 23:25:46

Could anyone tell me why the following code produces a result of 2.5 instead of 2 ?

CREATE TABLE MyValues (MyValue Float)
INSERT INTO MyValues (MyValue) SELECT 2
INSERT INTO MyValues (MyValue) SELECT 3
INSERT INTO MyValues (MyValue) SELECT 4
INSERT INTO MyValues (MyValue) SELECT 5
INSERT INTO MyValues (MyValue) SELECT 6
SELECT VAR(MyValue) FROM MyValues AS MyVariation

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-05-11 : 03:05:22

quote:
VAR
Returns the statistical variance of all values in the given expression.

quote:
VARP
Returns the statistical variance for the population for all values in the given expression.

Do you need VARP() or VAR() ?

malg
Starting Member

3 Posts

Posted - 2006-05-11 : 03:50:03

Thanks for the enquiry.

I now see that VARP provides the correct answer (2), but I am still curious as to why VAR provides a result of 2.5 instead.

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-05-11 : 07:08:16

Hi malg,

This gives you the formula and a bit of explanation

:

http://www.sqlservercentral.com/columnists/dpoole/sqlserverstatisticalfunctions.asp

And, here are the calculations done in 'longhand'...

--data
declare @t table (i int)
insert @t select 2 union select 3 union select 4 union select 5 union select 6

--calculation
select
    1.0 * (n * sum_i_squared - squared_sum_i) / power(n, 2) as varp, 
    1.0 * (n * sum_i_squared - squared_sum_i) / (n * (n-1)) as var
from (
    select count(*) as n, sum(i) as sum_i, sum(i_squared) as sum_i_squared, power(sum(i), 2) as squared_sum_i
    from (select i, power(i, 2) as i_squared from @t) a
    ) b

The 1.0s are to quickly convert to floats - if you take them away, then the maths is all done in integers and you end up with a value of 2 for var. Maybe that was where you were getting confused?

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

malg
Starting Member

3 Posts

Posted - 2006-05-11 : 10:03:54

Thanks Ryan. it is appreciated.
Mal

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources