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.