The VARIANCE and STDDEV Functions
The VARIANCE and STDDEV functions calculate, respectively, the variance and the standard deviation of the values in a particular column. These functions are, of course, applicable only to columns with a numeric data type.
Not every SQL product supports the functions VARIANCE and STDDEV. That is why we also illustrate in this section how these values can be calculated with standard SQL.
The VARIANCE function, or the VAR function, for short, calculates the variance. Variance is a measurement that indicates how close all values are to the average. In other words, it refers to the distribution of all values. The closer each value is to the average, the lower the variance is.
35. Get the variance of all penalties incurred by player 44.
Explanation: The variance is calculated on the basis of the following steps:
If you execute these steps for the previous statement, the first step returns the answer: 43.33333, the average of the three values 75, 25, and 30. Next, for each of the three values, the difference with the average is calculated. You can determine this with the following SELECT statement:
SELECT AMOUNT (SELECT AVG(AMOUNT) FROM PENALTIES WHERE PLAYERNO = 44) FROM PENALTIES WHERE PLAYERNO = 44
This gives the result: 31.666667, 18.33333, and 13.33333. You can use the following SELECT statement to calculate this intermediate result:
SELECT SUM(P) FROM (SELECT POWER(AMOUNT - (SELECT AVG(AMOUNT) FROM PENALTIES WHERE PLAYERNO = 44),2) AS P FROM PENALTIES WHERE PLAYERNO = 44) AS POWERS
The result is 1516.6666666667. In the final step, this amount is divided by the number of values, which gives an end result of 505.5555. To calculate all these steps without the VARIANCE function, the following statement can be used:
SELECT SUM(P) / (SELECT COUNT(*) FROM PENALTIES WHERE PLAYERNO = 44) FROM (SELECT POWER(AMOUNT - (SELECT AVG(AMOUNT) FROM PENALTIES WHERE PLAYERNO = 44),2) AS P FROM PENALTIES WHERE PLAYERNO = 44) AS POWERS
The STDDEV function calculates the standard deviation of a set of values. Standard deviation is another measure of distribution for determining how close the values are to the average. By definition, the standard deviation is equal to the square root of the variance. In other words, the following two expressions are equal: STDDEV(...) and SQRT(VARIANCE(...)).
36. Get the standard deviation for all penalties incurred by player 44.