SQL SERVER – Introduction to Statistical Functions – VAR, STDEVP, STDEV, VARP

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 (http://blog.SQLAuthority.com)

About these ads

8 thoughts on “SQL SERVER – Introduction to Statistical Functions – VAR, STDEVP, STDEV, VARP

  1. 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

  2. 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.

  3. 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 (:

  4. 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

  5. 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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s