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