Section 8.3. Calculating a Mean

8.3 Calculating a Mean

8.3.1 Problem

You want to calculate the average life of light bulbs in a sample, where the sample consists of all bulbs tested in a particular test pit.

8.3.2 Solution

Computing a mean is fairly easy, because the standard SQL function AVG produces the desired result. For example:

 SELECT AVG(Hours) Mean  FROM BulbLife WHERE TestPit=1 Mean         -----------  1089 

8.3.3 Discussion

Probably the easiest of all statistics to compute using SQL is the mean. The mean is just a simple average implemented by the standard SQL function AVG. The AVG function is a group function, which means that it operates on a group of rows. In the recipe solution, the group in question consisted of all rows for Test Pit #1. Using the GROUP BY clause, you can extend the query to report the mean bulb life for all test pits. For example:

 SELECT TestPit, AVG(hours) Mean  FROM BulbLife GROUP BY TestPit TestPit     Mean         ----------- -----------  1           1089 2           1093 

Using the HAVING clause, you can implement measurement rules requiring that results only be reported for those test pits that have a specified minimum number of measurements available. For example, the following query limits the report to test pits where more than eight light bulbs have been tested:

 SELECT TestPit, AVG(hours) Mean  FROM BulbLife GROUP BY TestPit HAVING COUNT(*) >= 8