8.6 Calculating Standard Deviation, Variance, and Standard Error8.6.1 ProblemYou 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 SolutionUse 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 DiscussionSince 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. |