Yesterday I wrote article about SQL SERVER – Introduction to Aggregate Functions. I received one email that four of the aggregate functions are statistical function and I should write something about that. VAR, STDEVP, STDEV, VARP are statistical functions as well they absolutely fit in the definition of aggregate function as well. The usage of this function is pretty simple so instead of explaining them I will go to example right away.
USE AdventureWorks;
GO
SELECT VAR(Bonus) 'Variance',
STDEVP(Bonus) 'Standard Deviation',
STDEV(Bonus) 'Standard Deviation',
VARP(Bonus) 'Variance for the Population'
FROM Sales.SalesPerson;
GO
All the functions returns result as datatype float. VAR and VARP can only be applied to numeric well all other can be applied to all numeric data type except INT datatypes.
Reference : Pinal Dave (https://blog.sqlauthority.com)
9 Comments. Leave new
Hi, Can You help me with my little questions?
I need to know if with a statistical funtion I can to know which tables are not used in a database.
Thanks
Milton
hi,
plz write something about:
what are statistics and what is the use of it? plz help
warm regards
Sandeep (noida)
Hi,
I want to summarize by the Mode on some categorical data.
I want to show the most common type of family structure (single parent, nuclear, extended etc) by village.
Any help please.
hi pinal,
i know about this function but my requirement is some what complicated
i have one table which include two thing one is district and the county now i want all county list not records group by district.
ex
District1
coun1, coun2, coun3
District2
coun1, coun2, coun3
So how can i do such thing
:) manish (:
Hi Pinal Dave,
Here is another sample to explain relationships between SQL Server statistic functions:
USE AdventureWorks;
GO
SELECT STDEVP(Bonus) ‘Standard Deviation for the Population’,
STDEV(Bonus) ‘Standard Deviation’,
VAR(Bonus) ‘Variance’,
STDEV(Bonus)* STDEV(Bonus) ‘VAR via STDEV’,
VARP(Bonus) ‘Variance for the Population’,
STDEVP(Bonus)* STDEVP(Bonus) ‘VARP via STDEVP’,
VAR(Bonus)*(COUNT(Bonus)-1)/COUNT(Bonus) ‘VARP via VAR ‘
FROM Sales.SalesPerson;
GO
Thanks
Oleg
hi!! i hope u could help me..
i need to know the statistical in choosing course in college??
tnx!!
hi!! i hope u could help me..
i need to know the statistical fucvtion in choosing course in college??
tnx!!
I just wanted to add the difference between the versions that aren’t “Population” and those that are. The ones that aren’t “Population” (Such as STDev and Var) are called “Sample” functions are estimates of the population based on the sample.
Long story short, Standard deviation and variance are different ways of representing the same thing (variance/deviation – google it for more info). But trying to work out how much the data varies based on only a few pieces of data is difficult, so an error factor is added in that helps out. This error factor gets smaller and smaller with more data so that with very large data sets the population and sample functions will return almost identical results.
When to use which one? Rule of thumb is you rarely or never use the population one because you almost never have, know or able to measure every measurement from all possible measurements.
Let’s say you are mearsuring the height of children in a class. They are “samples” of children of their age/type and therefore use the sample functions. You shouldn’t use the population function unless you can measure and have a record of all children of ages that could be in your class across all time.
In fact, very few times will you ever have the entire population of data so very few times will use use the population functions, and if you did have all the data chances are the sample functions would return the same results.
As for Mode and other statistical functions, I offer an sql server SQL Math library that provides those abilities. sqladmintools.com
I am able to find NTILE in SQL SERVER but I wants Quartiles. Will you please help me.