Standard Deviation - 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

Standard Deviation

Author

Topic

brethart
Starting Member

2 Posts

Posted - 2011-04-13 : 09:16:05

Hello,
What's the easiest way to calculate standard deviation in SQL?

I have columns like these:

Name Score
John 90
John 31
John 4
John 49
Rick 30
Rick 40
Rick 51
Tom 8
Tom 72

I want to add a 3rd column StdDev and put in the values for each name next to it.

Thanks for your help as I make the transition from excel!

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-04-13 : 10:08:27

Yes, there are two built in functions, STDEV and STDEVP. In each, you can specify ALL or DISTINCT depending on what you are trying to do. For example:

select stdev(distinct score) from YourTable

BUT, this gives the standard deviation of everything in the table. When you said you want to put a third column in your data, what is the range that you are trying to calculate the standard deviation of?

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2011-04-13 : 10:54:47

to expand on sunita's post... and I'm guessing you want to look at this per person.


Declare @t table (
	Name varchar(10),
	Score decimal(9,4)
)

Insert Into @t Select 'John', 90
Insert Into @t Select 'John', 31
Insert Into @t Select 'John', 4
Insert Into @t Select 'John', 49
Insert Into @t Select 'Rick', 30
Insert Into @t Select 'Rick', 40
Insert Into @t Select 'Rick', 51
Insert Into @t Select 'Tom', 8
Insert Into @t Select 'Tom', 72


Select 
	*,
	Score_Avg = Avg(Score) Over (Partition By Name),
	Score_Min = Min(Score) Over (Partition By Name),
	Score_Max = Max(Score) Over (Partition By Name),
	Score_Stdv = convert(decimal(9,4),Stdev(Score) Over (Partition By Name))
From @t

Corey

I Has Returned!!

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-04-13 : 11:21:31

Thank you Corey. I hadn't looked at the data in the sample data carefully enought to see that there are repeated names and that the std deviation of the score for each name might be what the OP is looking for.

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2011-04-13 : 11:24:12

It's all guesswork anyway