Login     Register

        Contact Us     Search

XLeratorDB/statistics Documentation

SQL Server population standard deviation function


STDEV_P

Updated: 24 May 2013


Use the aggregate function STDEV_P to calculate the standard deviation for an entire population. STDEV_P is a numerically stable single-pass calculation of the population standard deviation which will produce more reliable results under certain conditions than the built-in SQL Server function STDEVP, without compromising performance.
For a population N having a distribution where the mean is not known, the population standard deviation is defined as:

XLeratorDB formula for Population Standard Deviation (STDEV.P) for SQL Server
Syntax
XLeratorDB syntax for Population Standard Deviation (STDEV.P) for SQL Server
Arguments
@Known_x
the values to be used in the calculation. @Known_x must be of a type float or of a type that implicitly converts to float.
Return Types
float
Remarks
·         If you want measure the standard deviation for a sample, then use the STDEV_S function.
·         To calculate the population variance use the VAR_P function.
·         To calculate the sample standard deviation use the STDEV_S function.
·         To calculate a running population standard deviation use the RunningSTDEVP function.
·         To calculate a moving population standard deviation use the MovingSTDEVP function.
Examples
SELECT wct.STDEV_P(x) as STDEV_P
FROM (
      SELECT 30000.0000216303 UNION ALL
      SELECT 30000.0000565854 UNION ALL
      SELECT 30000.000038137 UNION ALL
      SELECT 30000.0000495983 UNION ALL
      SELECT 30000.0000185861 UNION ALL
      SELECT 30000.0000863479 UNION ALL
      SELECT 30000.0000776366 UNION ALL
      SELECT 30000.0000637985 UNION ALL
      SELECT 30000.0000939786 UNION ALL
      SELECT 30000.000031191 UNION ALL
      SELECT 30000.0000550457 UNION ALL
      SELECT 30000.0000207558 UNION ALL
      SELECT 30000.0000805531 UNION ALL
      SELECT 30000.0000241287
      )n(x)
This produces the following result
               STDEV_P
----------------------
 2.53540691406054E-05
 
In this example, using the same data, we compare the XLeratorDB value to the value returned by the native SQL Server function.
SELECT wct.STDEV_P(x) as XLeratorDB
,STDEVP(x) as [SQL Server]
FROM (
      SELECT 30000.0000216303 UNION ALL
      SELECT 30000.0000565854 UNION ALL
      SELECT 30000.000038137 UNION ALL
      SELECT 30000.0000495983 UNION ALL
      SELECT 30000.0000185861 UNION ALL
      SELECT 30000.0000863479 UNION ALL
      SELECT 30000.0000776366 UNION ALL
      SELECT 30000.0000637985 UNION ALL
      SELECT 30000.0000939786 UNION ALL
      SELECT 30000.000031191 UNION ALL
      SELECT 30000.0000550457 UNION ALL
      SELECT 30000.0000207558 UNION ALL
      SELECT 30000.0000805531 UNION ALL
      SELECT 30000.0000241287
      )n(x)
This produces the following result.
            XLeratorDB             SQL Server
---------------------- ----------------------
 2.53540691406054E-05                      0
 


Copyright 2008-2017 Westclintech LLC         Privacy Policy        Terms of Service