The VARIANCE and STDDEV Functions






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.

Portability

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.

SELECT   VARIANCE(AMOUNT)
FROM     PENALTIES
WHERE    PLAYERNO = 44

The result is:

VARIANCE(AMOUNT)
----------------
         505.555

Explanation: The variance is calculated on the basis of the following steps:

  • Calculate the average of the column concerned.

  • Determine for each value in the column how much the absolute value differs from the average.

  • Calculate the sum of the squares of the differences.

  • Divide the sum by the number of values (in the column).

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.

SELECT   STDDEV(AMOUNT)
FROM     PENALTIES
WHERE    PLAYERNO = 44

The result is:

STDDEV(AMOUNT)
--------------
     22.484563

Exercise 9.21:

Get the standard deviation of all penalties of player 44 without using the STDDEV function.



 Python   SQL   Java   php   Perl 
 game development   web development   internet   *nix   graphics   hardware 
 telecommunications   C++ 
 Flash   Active Directory   Windows