Section 8.6. Calculating Standard Deviation, Variance, and Standard Error

   

8.6 Calculating Standard Deviation, Variance, and Standard Error

8.6.1 Problem

You want to calculate both the standard deviation and the variance of a sample. You also want to assess the standard error of the sample.

8.6.2 Solution

Use SQL Server's built-in STDEV, STDEVP, VAR, and VARP functions for calculating standard deviation and variance. For example, the following query will return the standard deviation and variance for the sample values from each of the test pits:

 SELECT TestPit, VAR(Hours) variance, STDEV(Hours) st_deviation FROM BulbLife GROUP BY TestPit TestPit variance  st_deviation                                           ------- --------- ------------ 1       672.99    25.94 2       1173.66   34.26 

To get the standard error of the sample, simply use the following query, which implements the formula for standard error shown earlier in this chapter:

 SELECT AVG(Hours) mean, STDEV(Hours)/SQRT(COUNT(*)) st_error  FROM BulbLife mean        st_error                                               ----------- ----------  1091        6.64 

8.6.3 Discussion

Since SQL Server provides functions to calculate standard deviation and variance, it is wise to use them and not program your own. However, be careful when you use them. You need to know whether the data from which you calculate the statistics represents the whole population or just a sample. In our example, the table holds data for just a sample of the entire population of light bulbs . Therefore, we used the STDEV and VAR functions designed for use on samples. If your data includes the entire population, use STDEVP and VARP instead.

   


Transact-SQL Cookbook
Transact-SQL Cookbook
ISBN: 1565927567
EAN: 2147483647
Year: 2005
Pages: 152

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net