Community Pick: Many members of our community have endorsed this article.
Editor's Choice: This article has been selected by our editors as an exceptional contribution.

Calculating Special Means in Microsoft SQL Server Part 1: Weighted Average and Harmonic Mean

Patrick Matthews
CERTIFIED EXPERT
Published:
Updated:

Introduction



One of the leading measures of central tendency is the average, or arithmetic mean.  Microsoft SQL Server makes it easy to calculate the average for a data set by using the AVG aggregate function.  However, the arithmetic mean is not the only type of mean that exists, and for certain analyses, other types of means may be more appropriate.

This article will demonstrate how to calculate several two other useful, yet less commonly used means, using Microsoft SQL Server:


Part 2 of this article will extend the discussion to include the following other types of means:


Note: Most of the material in this article originally appeared in one of my earlier articles, Calculating Special Means in Microsoft Access Part 1: Weighted Average and Harmonic Mean.  For this article, I have adapted the text, examples, and SQL code to reflect proper Transact-SQL syntax.


Sample Data



To facilitate the examples in this article, I used the following T-SQL batch to create and populate six tables:

tblWeightedAverage
tblExams
tblStudents
tblStudentMarks
tblHarmMean
tblHarmMeanWeighted

If you wish to follow along with the examples on your own, please execute the batch in SQL Query Analyzer (SQL Server 2000), or in the SQL Server Management Studio (SQL Server 2005 or later).

/* This script creates and populates the tables needed for the examples in the Special
                      Means Part 1 article */
                      
                      CREATE TABLE tblWeightedAverage (ID int IDENTITY(1, 1), Region varchar(50), District int, 
                          Weight decimal(10, 2), Value decimal(10, 2))
                      
                      INSERT INTO tblWeightedAverage (Region, District, Weight, Value) VALUES ('A', 1, -2, 140.4)
                      INSERT INTO tblWeightedAverage (Region, District, Weight, Value) VALUES ('A', 1, 18, 85)
                      INSERT INTO tblWeightedAverage (Region, District, Weight, Value) VALUES ('A', 1, 4, 80.4)
                      INSERT INTO tblWeightedAverage (Region, District, Weight, Value) VALUES ('A', 1, 29, 68)
                      INSERT INTO tblWeightedAverage (Region, District, Weight, Value) VALUES ('A', 1, 38, 57.1)
                      INSERT INTO tblWeightedAverage (Region, District, Weight, Value) VALUES ('A', 1, 19, 45.7)
                      INSERT INTO tblWeightedAverage (Region, District, Weight, Value) VALUES ('A', 1, 13, 111.5)
                      INSERT INTO tblWeightedAverage (Region, District, Weight, Value) VALUES ('A', 1, 36, 124.1)
                      INSERT INTO tblWeightedAverage (Region, District, Weight, Value) VALUES ('A', 1, 45, 108.4)
                      INSERT INTO tblWeightedAverage (Region, District, Weight, Value) VALUES ('A', 1, 4, 119.5)
                      INSERT INTO tblWeightedAverage (Region, District, Weight, Value) VALUES ('A', 1, 47, 48.5)
                      INSERT INTO tblWeightedAverage (Region, District, Weight, Value) VALUES ('A', 1, 37, 114.4)
                      INSERT INTO tblWeightedAverage (Region, District, Weight, Value) VALUES ('A', 1, 9, 130.1)
                      INSERT INTO tblWeightedAverage (Region, District, Weight, Value) VALUES ('A', 1, 12, 72.1)
                      INSERT INTO tblWeightedAverage (Region, District, Weight, Value) VALUES ('A', 1, 40, 96.8)
                      INSERT INTO tblWeightedAverage (Region, District, Weight, Value) VALUES ('A', 1, 11, 69.9)
                      INSERT INTO tblWeightedAverage (Region, District, Weight, Value) VALUES ('A', 1, 47, 89.5)
                      INSERT INTO tblWeightedAverage (Region, District, Weight, Value) VALUES ('A', 1, 11, 78.9)
                      INSERT INTO tblWeightedAverage (Region, District, Weight, Value) VALUES ('A', 1, 17, 30.1)
                      INSERT INTO tblWeightedAverage (Region, District, Weight, Value) VALUES ('A', 1, 20, 61.8)
                      INSERT INTO tblWeightedAverage (Region, District, Weight, Value) VALUES ('A', 1, 36, 64.5)
                      INSERT INTO tblWeightedAverage (Region, District, Weight, Value) VALUES ('A', 1, 22, 66.2)
                      INSERT INTO tblWeightedAverage (Region, District, Weight, Value) VALUES ('A', 1, 10, 99.2)
                      INSERT INTO tblWeightedAverage (Region, District, Weight, Value) VALUES ('A', 1, 26, 139.8)
                      INSERT INTO tblWeightedAverage (Region, District, Weight, Value) VALUES ('A', 1, 30, 97.2)
                      INSERT INTO tblWeightedAverage (Region, District, Weight, Value) VALUES ('A', 2, 48, 104.3)
                      INSERT INTO tblWeightedAverage (Region, District, Weight, Value) VALUES ('A', 2, 17, 175.2)
                      INSERT INTO tblWeightedAverage (Region, District, Weight, Value) VALUES ('A', 2, 31, 2.1)
                      INSERT INTO tblWeightedAverage (Region, District, Weight, Value) VALUES ('A', 2, 16, 126.6)
                      INSERT INTO tblWeightedAverage (Region, District, Weight, Value) VALUES ('A', 2, 28, 58.2)
                      INSERT INTO tblWeightedAverage (Region, District, Weight, Value) VALUES ('A', 2, 36, 102.1)
                      INSERT INTO tblWeightedAverage (Region, District, Weight, Value) VALUES ('A', 2, 47, 27.8)
                      INSERT INTO tblWeightedAverage (Region, District, Weight, Value) VALUES ('A', 2, 2, 128.1)
                      INSERT INTO tblWeightedAverage (Region, District, Weight, Value) VALUES ('A', 2, 17, 152.5)
                      INSERT INTO tblWeightedAverage (Region, District, Weight, Value) VALUES ('A', 2, 10, 62.7)
                      INSERT INTO tblWeightedAverage (Region, District, Weight, Value) VALUES ('A', 2, 1, 99.6)
                      INSERT INTO tblWeightedAverage (Region, District, Weight, Value) VALUES ('A', 2, 44, 136.7)
                      INSERT INTO tblWeightedAverage (Region, District, Weight, Value) VALUES ('A', 2, 44, 131.2)
                      INSERT INTO tblWeightedAverage (Region, District, Weight, Value) VALUES ('A', 2, 35, 87.7)
                      INSERT INTO tblWeightedAverage (Region, District, Weight, Value) VALUES ('A', 2, 11, 114.1)
                      INSERT INTO tblWeightedAverage (Region, District, Weight, Value) VALUES ('A', 2, 30, 115.5)
                      INSERT INTO tblWeightedAverage (Region, District, Weight, Value) VALUES ('A', 2, 18, 123.5)
                      INSERT INTO tblWeightedAverage (Region, District, Weight, Value) VALUES ('A', 2, 5, 163.6)
                      INSERT INTO tblWeightedAverage (Region, District, Weight, Value) VALUES ('A', 2, 12, 109.2)
                      INSERT INTO tblWeightedAverage (Region, District, Weight, Value) VALUES ('A', 2, 20, 105.9)
                      INSERT INTO tblWeightedAverage (Region, District, Weight, Value) VALUES ('A', 2, 33, 111.5)
                      INSERT INTO tblWeightedAverage (Region, District, Weight, Value) VALUES ('A', 2, 19, 121.9)
                      INSERT INTO tblWeightedAverage (Region, District, Weight, Value) VALUES ('A', 2, 17, 113.9)
                      INSERT INTO tblWeightedAverage (Region, District, Weight, Value) VALUES ('A', 2, 3, 102.7)
                      INSERT INTO tblWeightedAverage (Region, District, Weight, Value) VALUES ('A', 2, 43, 82.3)
                      INSERT INTO tblWeightedAverage (Region, District, Weight, Value) VALUES ('B', 3, NULL, 97.4)
                      INSERT INTO tblWeightedAverage (Region, District, Weight, Value) VALUES ('B', 3, 40, 43.3)
                      INSERT INTO tblWeightedAverage (Region, District, Weight, Value) VALUES ('B', 3, 48, 144.1)
                      INSERT INTO tblWeightedAverage (Region, District, Weight, Value) VALUES ('B', 3, 11, 101.1)
                      INSERT INTO tblWeightedAverage (Region, District, Weight, Value) VALUES ('B', 3, 45, 133.5)
                      INSERT INTO tblWeightedAverage (Region, District, Weight, Value) VALUES ('B', 3, 35, 80.2)
                      INSERT INTO tblWeightedAverage (Region, District, Weight, Value) VALUES ('B', 3, 28, 93.8)
                      INSERT INTO tblWeightedAverage (Region, District, Weight, Value) VALUES ('B', 3, 45, 117.4)
                      INSERT INTO tblWeightedAverage (Region, District, Weight, Value) VALUES ('B', 3, 41, 95.7)
                      INSERT INTO tblWeightedAverage (Region, District, Weight, Value) VALUES ('B', 3, 13, 109.9)
                      INSERT INTO tblWeightedAverage (Region, District, Weight, Value) VALUES ('B', 3, 18, 103)
                      INSERT INTO tblWeightedAverage (Region, District, Weight, Value) VALUES ('B', 3, 15, 53.4)
                      INSERT INTO tblWeightedAverage (Region, District, Weight, Value) VALUES ('B', 3, 48, 94.2)
                      INSERT INTO tblWeightedAverage (Region, District, Weight, Value) VALUES ('B', 3, 10, 104.3)
                      INSERT INTO tblWeightedAverage (Region, District, Weight, Value) VALUES ('B', 3, 14, 140.3)
                      INSERT INTO tblWeightedAverage (Region, District, Weight, Value) VALUES ('B', 3, 21, 112.3)
                      INSERT INTO tblWeightedAverage (Region, District, Weight, Value) VALUES ('B', 3, 5, 65.5)
                      INSERT INTO tblWeightedAverage (Region, District, Weight, Value) VALUES ('B', 3, 24, 78.3)
                      INSERT INTO tblWeightedAverage (Region, District, Weight, Value) VALUES ('B', 3, 25, 88.4)
                      INSERT INTO tblWeightedAverage (Region, District, Weight, Value) VALUES ('B', 3, 20, 143.1)
                      INSERT INTO tblWeightedAverage (Region, District, Weight, Value) VALUES ('B', 3, 8, 98.2)
                      INSERT INTO tblWeightedAverage (Region, District, Weight, Value) VALUES ('B', 3, 2, 77.2)
                      INSERT INTO tblWeightedAverage (Region, District, Weight, Value) VALUES ('B', 3, 41, 74.5)
                      INSERT INTO tblWeightedAverage (Region, District, Weight, Value) VALUES ('B', 3, 12, 119.5)
                      INSERT INTO tblWeightedAverage (Region, District, Weight, Value) VALUES ('B', 3, 1, 119.1)
                      INSERT INTO tblWeightedAverage (Region, District, Weight, Value) VALUES ('B', 4, 21, 112.2)
                      INSERT INTO tblWeightedAverage (Region, District, Weight, Value) VALUES ('B', 4, 28, 46.2)
                      INSERT INTO tblWeightedAverage (Region, District, Weight, Value) VALUES ('B', 4, 21, 116.2)
                      INSERT INTO tblWeightedAverage (Region, District, Weight, Value) VALUES ('B', 4, 6, 73.4)
                      INSERT INTO tblWeightedAverage (Region, District, Weight, Value) VALUES ('B', 4, 35, 76.4)
                      INSERT INTO tblWeightedAverage (Region, District, Weight, Value) VALUES ('B', 4, 21, 76.6)
                      INSERT INTO tblWeightedAverage (Region, District, Weight, Value) VALUES ('B', 4, 9, 131.2)
                      INSERT INTO tblWeightedAverage (Region, District, Weight, Value) VALUES ('B', 4, 15, 27.4)
                      INSERT INTO tblWeightedAverage (Region, District, Weight, Value) VALUES ('B', 4, 23, 148.9)
                      INSERT INTO tblWeightedAverage (Region, District, Weight, Value) VALUES ('B', 4, 47, 147.1)
                      INSERT INTO tblWeightedAverage (Region, District, Weight, Value) VALUES ('B', 4, 24, 76)
                      INSERT INTO tblWeightedAverage (Region, District, Weight, Value) VALUES ('B', 4, 25, 97.5)
                      INSERT INTO tblWeightedAverage (Region, District, Weight, Value) VALUES ('B', 4, 48, 41.9)
                      INSERT INTO tblWeightedAverage (Region, District, Weight, Value) VALUES ('B', 4, 7, 107.4)
                      INSERT INTO tblWeightedAverage (Region, District, Weight, Value) VALUES ('B', 4, 44, 107.3)
                      INSERT INTO tblWeightedAverage (Region, District, Weight, Value) VALUES ('B', 4, 43, 127.4)
                      INSERT INTO tblWeightedAverage (Region, District, Weight, Value) VALUES ('B', 4, 47, 125.3)
                      INSERT INTO tblWeightedAverage (Region, District, Weight, Value) VALUES ('B', 4, 42, 97.3)
                      INSERT INTO tblWeightedAverage (Region, District, Weight, Value) VALUES ('B', 4, 9, 92.4)
                      INSERT INTO tblWeightedAverage (Region, District, Weight, Value) VALUES ('B', 4, 47, 99.7)
                      INSERT INTO tblWeightedAverage (Region, District, Weight, Value) VALUES ('B', 4, 49, 137.1)
                      INSERT INTO tblWeightedAverage (Region, District, Weight, Value) VALUES ('B', 4, 22, 96.2)
                      INSERT INTO tblWeightedAverage (Region, District, Weight, Value) VALUES ('B', 4, 41, 105.5)
                      INSERT INTO tblWeightedAverage (Region, District, Weight, Value) VALUES ('B', 4, 50, 121.1)
                      INSERT INTO tblWeightedAverage (Region, District, Weight, Value) VALUES ('B', 4, 29, 130.4)
                      INSERT INTO tblWeightedAverage (Region, District, Weight, Value) VALUES ('C', 5, 37, NULL)
                      INSERT INTO tblWeightedAverage (Region, District, Weight, Value) VALUES ('C', 5, 32, 74.2)
                      INSERT INTO tblWeightedAverage (Region, District, Weight, Value) VALUES ('C', 5, 38, 99.4)
                      INSERT INTO tblWeightedAverage (Region, District, Weight, Value) VALUES ('C', 5, 32, 90.9)
                      INSERT INTO tblWeightedAverage (Region, District, Weight, Value) VALUES ('C', 5, 22, 76.9)
                      INSERT INTO tblWeightedAverage (Region, District, Weight, Value) VALUES ('C', 5, 24, 118.3)
                      INSERT INTO tblWeightedAverage (Region, District, Weight, Value) VALUES ('C', 5, 16, 136.6)
                      INSERT INTO tblWeightedAverage (Region, District, Weight, Value) VALUES ('C', 5, 36, 101.5)
                      INSERT INTO tblWeightedAverage (Region, District, Weight, Value) VALUES ('C', 5, 7, 122.4)
                      INSERT INTO tblWeightedAverage (Region, District, Weight, Value) VALUES ('C', 5, 3, 122.2)
                      INSERT INTO tblWeightedAverage (Region, District, Weight, Value) VALUES ('C', 5, 32, 55.3)
                      INSERT INTO tblWeightedAverage (Region, District, Weight, Value) VALUES ('C', 5, 16, 118.8)
                      INSERT INTO tblWeightedAverage (Region, District, Weight, Value) VALUES ('C', 5, 32, 115.4)
                      INSERT INTO tblWeightedAverage (Region, District, Weight, Value) VALUES ('C', 5, 28, 97)
                      INSERT INTO tblWeightedAverage (Region, District, Weight, Value) VALUES ('C', 5, 15, 93.6)
                      INSERT INTO tblWeightedAverage (Region, District, Weight, Value) VALUES ('C', 5, 1, 112.8)
                      INSERT INTO tblWeightedAverage (Region, District, Weight, Value) VALUES ('C', 5, 39, 110.2)
                      INSERT INTO tblWeightedAverage (Region, District, Weight, Value) VALUES ('C', 5, 5, 130.6)
                      INSERT INTO tblWeightedAverage (Region, District, Weight, Value) VALUES ('C', 5, 12, 58.9)
                      INSERT INTO tblWeightedAverage (Region, District, Weight, Value) VALUES ('C', 5, 48, 110.7)
                      INSERT INTO tblWeightedAverage (Region, District, Weight, Value) VALUES ('C', 5, 25, 122.4)
                      INSERT INTO tblWeightedAverage (Region, District, Weight, Value) VALUES ('C', 5, 50, 134.3)
                      INSERT INTO tblWeightedAverage (Region, District, Weight, Value) VALUES ('C', 5, 2, 65.2)
                      INSERT INTO tblWeightedAverage (Region, District, Weight, Value) VALUES ('C', 5, 2, 100.7)
                      INSERT INTO tblWeightedAverage (Region, District, Weight, Value) VALUES ('C', 5, 44, 69.9)
                      INSERT INTO tblWeightedAverage (Region, District, Weight, Value) VALUES ('C', 6, 42, 90.4)
                      INSERT INTO tblWeightedAverage (Region, District, Weight, Value) VALUES ('C', 6, 21, 96.6)
                      INSERT INTO tblWeightedAverage (Region, District, Weight, Value) VALUES ('C', 6, 12, 100.8)
                      INSERT INTO tblWeightedAverage (Region, District, Weight, Value) VALUES ('C', 6, 5, 106.3)
                      INSERT INTO tblWeightedAverage (Region, District, Weight, Value) VALUES ('C', 6, 3, 130.3)
                      INSERT INTO tblWeightedAverage (Region, District, Weight, Value) VALUES ('C', 6, 3, 95.6)
                      INSERT INTO tblWeightedAverage (Region, District, Weight, Value) VALUES ('C', 6, 16, 74.1)
                      INSERT INTO tblWeightedAverage (Region, District, Weight, Value) VALUES ('C', 6, 44, 93.1)
                      INSERT INTO tblWeightedAverage (Region, District, Weight, Value) VALUES ('C', 6, 7, 51.7)
                      INSERT INTO tblWeightedAverage (Region, District, Weight, Value) VALUES ('C', 6, 43, 113.9)
                      INSERT INTO tblWeightedAverage (Region, District, Weight, Value) VALUES ('C', 6, 37, 124.1)
                      INSERT INTO tblWeightedAverage (Region, District, Weight, Value) VALUES ('C', 6, 11, 107.1)
                      INSERT INTO tblWeightedAverage (Region, District, Weight, Value) VALUES ('C', 6, 37, 140.4)
                      INSERT INTO tblWeightedAverage (Region, District, Weight, Value) VALUES ('C', 6, 39, 122.7)
                      INSERT INTO tblWeightedAverage (Region, District, Weight, Value) VALUES ('C', 6, 44, 57.6)
                      INSERT INTO tblWeightedAverage (Region, District, Weight, Value) VALUES ('C', 6, 4, 80)
                      INSERT INTO tblWeightedAverage (Region, District, Weight, Value) VALUES ('C', 6, 18, 125.4)
                      INSERT INTO tblWeightedAverage (Region, District, Weight, Value) VALUES ('C', 6, 29, 88.8)
                      INSERT INTO tblWeightedAverage (Region, District, Weight, Value) VALUES ('C', 6, 47, 83.4)
                      INSERT INTO tblWeightedAverage (Region, District, Weight, Value) VALUES ('C', 6, 24, 79.1)
                      INSERT INTO tblWeightedAverage (Region, District, Weight, Value) VALUES ('C', 6, 42, 82)
                      INSERT INTO tblWeightedAverage (Region, District, Weight, Value) VALUES ('C', 6, 48, 86.4)
                      INSERT INTO tblWeightedAverage (Region, District, Weight, Value) VALUES ('C', 6, 28, 94.1)
                      INSERT INTO tblWeightedAverage (Region, District, Weight, Value) VALUES ('C', 6, 45, 100.2)
                      INSERT INTO tblWeightedAverage (Region, District, Weight, Value) VALUES ('C', 6, 36, 83.6)
                      INSERT INTO tblWeightedAverage (Region, District, Weight, Value) VALUES ('D', 7, 77, 132.6)
                      INSERT INTO tblWeightedAverage (Region, District, Weight, Value) VALUES ('D', 7, 65, 143.7)
                      INSERT INTO tblWeightedAverage (Region, District, Weight, Value) VALUES ('D', 7, 83, 100.5)
                      INSERT INTO tblWeightedAverage (Region, District, Weight, Value) VALUES ('D', 7, 68, 185.3)
                      INSERT INTO tblWeightedAverage (Region, District, Weight, Value) VALUES ('D', 7, 32, 157.5)
                      INSERT INTO tblWeightedAverage (Region, District, Weight, Value) VALUES ('D', 7, 78, 109.2)
                      INSERT INTO tblWeightedAverage (Region, District, Weight, Value) VALUES ('D', 7, 39, 179.4)
                      INSERT INTO tblWeightedAverage (Region, District, Weight, Value) VALUES ('D', 7, 5, 110.3)
                      INSERT INTO tblWeightedAverage (Region, District, Weight, Value) VALUES ('D', 7, 88, 46.2)
                      INSERT INTO tblWeightedAverage (Region, District, Weight, Value) VALUES ('D', 7, 93, 139.2)
                      INSERT INTO tblWeightedAverage (Region, District, Weight, Value) VALUES ('D', 7, 19, 154)
                      INSERT INTO tblWeightedAverage (Region, District, Weight, Value) VALUES ('D', 7, 42, 115.4)
                      INSERT INTO tblWeightedAverage (Region, District, Weight, Value) VALUES ('D', 7, 16, 191.5)
                      INSERT INTO tblWeightedAverage (Region, District, Weight, Value) VALUES ('D', 7, 74, 138.1)
                      INSERT INTO tblWeightedAverage (Region, District, Weight, Value) VALUES ('D', 7, 85, 118.6)
                      INSERT INTO tblWeightedAverage (Region, District, Weight, Value) VALUES ('D', 7, 5, 141.8)
                      INSERT INTO tblWeightedAverage (Region, District, Weight, Value) VALUES ('D', 7, 75, 98.5)
                      INSERT INTO tblWeightedAverage (Region, District, Weight, Value) VALUES ('D', 7, 45, 107.7)
                      INSERT INTO tblWeightedAverage (Region, District, Weight, Value) VALUES ('D', 7, 46, 131.8)
                      INSERT INTO tblWeightedAverage (Region, District, Weight, Value) VALUES ('D', 7, 83, 118.9)
                      INSERT INTO tblWeightedAverage (Region, District, Weight, Value) VALUES ('D', 7, 23, 145.4)
                      INSERT INTO tblWeightedAverage (Region, District, Weight, Value) VALUES ('D', 7, 92, 105.7)
                      INSERT INTO tblWeightedAverage (Region, District, Weight, Value) VALUES ('D', 7, 56, 100.2)
                      INSERT INTO tblWeightedAverage (Region, District, Weight, Value) VALUES ('D', 7, 54, 159.3)
                      INSERT INTO tblWeightedAverage (Region, District, Weight, Value) VALUES ('D', 7, 19, 153.1)
                      INSERT INTO tblWeightedAverage (Region, District, Weight, Value) VALUES ('D', 8, 20, 102.3)
                      INSERT INTO tblWeightedAverage (Region, District, Weight, Value) VALUES ('D', 8, 12, 88.5)
                      INSERT INTO tblWeightedAverage (Region, District, Weight, Value) VALUES ('D', 8, 3, 152.6)
                      INSERT INTO tblWeightedAverage (Region, District, Weight, Value) VALUES ('D', 8, 6, 131.3)
                      INSERT INTO tblWeightedAverage (Region, District, Weight, Value) VALUES ('D', 8, 39, 13.9)
                      INSERT INTO tblWeightedAverage (Region, District, Weight, Value) VALUES ('D', 8, 5, 121.8)
                      INSERT INTO tblWeightedAverage (Region, District, Weight, Value) VALUES ('D', 8, 98, 119.7)
                      INSERT INTO tblWeightedAverage (Region, District, Weight, Value) VALUES ('D', 8, 62, 141.5)
                      INSERT INTO tblWeightedAverage (Region, District, Weight, Value) VALUES ('D', 8, 86, 126.8)
                      INSERT INTO tblWeightedAverage (Region, District, Weight, Value) VALUES ('D', 8, 93, 138.3)
                      INSERT INTO tblWeightedAverage (Region, District, Weight, Value) VALUES ('D', 8, 34, 94.6)
                      INSERT INTO tblWeightedAverage (Region, District, Weight, Value) VALUES ('D', 8, 92, 167.5)
                      INSERT INTO tblWeightedAverage (Region, District, Weight, Value) VALUES ('D', 8, 46, 159.6)
                      INSERT INTO tblWeightedAverage (Region, District, Weight, Value) VALUES ('D', 8, 92, 135.1)
                      INSERT INTO tblWeightedAverage (Region, District, Weight, Value) VALUES ('D', 8, 19, 126.5)
                      INSERT INTO tblWeightedAverage (Region, District, Weight, Value) VALUES ('D', 8, 93, 134.2)
                      INSERT INTO tblWeightedAverage (Region, District, Weight, Value) VALUES ('D', 8, 98, 86.7)
                      INSERT INTO tblWeightedAverage (Region, District, Weight, Value) VALUES ('D', 8, 84, 159.2)
                      INSERT INTO tblWeightedAverage (Region, District, Weight, Value) VALUES ('D', 8, 85, 151.8)
                      INSERT INTO tblWeightedAverage (Region, District, Weight, Value) VALUES ('D', 8, 88, 131.8)
                      INSERT INTO tblWeightedAverage (Region, District, Weight, Value) VALUES ('D', 8, 34, 80.9)
                      INSERT INTO tblWeightedAverage (Region, District, Weight, Value) VALUES ('D', 8, 73, 44.5)
                      INSERT INTO tblWeightedAverage (Region, District, Weight, Value) VALUES ('D', 8, 75, 147.8)
                      INSERT INTO tblWeightedAverage (Region, District, Weight, Value) VALUES ('D', 8, 31, 61.5)
                      INSERT INTO tblWeightedAverage (Region, District, Weight, Value) VALUES ('D', 8, 71, 149.2)
                      INSERT INTO tblWeightedAverage (Region, District, Weight, Value) VALUES ('E', 9, 0, 100)
                      INSERT INTO tblWeightedAverage (Region, District, Weight, Value) VALUES ('E', 9, 0, 200)
                      
                      CREATE TABLE tblExams (ID int IDENTITY(1, 1), ExamDescr varchar(50), Weight decimal(5, 2))
                      
                      INSERT INTO tblExams (ExamDescr, Weight) VALUES ('Quiz 1', 0.05)
                      INSERT INTO tblExams (ExamDescr, Weight) VALUES ('Paper 1', 0.1)
                      INSERT INTO tblExams (ExamDescr, Weight) VALUES ('Quiz 2', 0.05)
                      INSERT INTO tblExams (ExamDescr, Weight) VALUES ('Mid Term', 0.2)
                      INSERT INTO tblExams (ExamDescr, Weight) VALUES ('Quiz 3', 0.05)
                      INSERT INTO tblExams (ExamDescr, Weight) VALUES ('Paper 2', 0.1)
                      INSERT INTO tblExams (ExamDescr, Weight) VALUES ('Quiz 4', 0.05)
                      INSERT INTO tblExams (ExamDescr, Weight) VALUES ('Final Exam', 0.4)
                      
                      CREATE TABLE tblStudents (ID int IDENTITY(1, 1), Lname varchar(50), Fname varchar(50), 
                          Mname varchar(50))
                      
                      INSERT INTO tblStudents (Lname, Fname, Mname) VALUES ('Beauchamp', 'Demetrius', 'R')
                      INSERT INTO tblStudents (Lname, Fname, Mname) VALUES ('Watt', 'Kelby', 'O')
                      INSERT INTO tblStudents (Lname, Fname, Mname) VALUES ('Stauffer', 'Marie', 'Z')
                      INSERT INTO tblStudents (Lname, Fname, Mname) VALUES ('Robison', 'Samson', 'W')
                      INSERT INTO tblStudents (Lname, Fname, Mname) VALUES ('Reich', 'Otis', 'H')
                      INSERT INTO tblStudents (Lname, Fname, Mname) VALUES ('Mickens', 'Bryon', 'U')
                      INSERT INTO tblStudents (Lname, Fname, Mname) VALUES ('Purdy', 'Hannah', 'E')
                      INSERT INTO tblStudents (Lname, Fname, Mname) VALUES ('Olds', 'Queen', 'W')
                      INSERT INTO tblStudents (Lname, Fname, Mname) VALUES ('Petit', 'Arvil', 'B')
                      INSERT INTO tblStudents (Lname, Fname, Mname) VALUES ('Witte', 'Dino', 'R')
                      
                      CREATE TABLE tblStudentMarks (ID int IDENTITY(1, 1), StudentID int, ExamID int, 
                          Score decimal(5, 2))
                      
                      INSERT INTO tblStudentMarks (StudentID, ExamID, Score) VALUES (1, 1, 84)
                      INSERT INTO tblStudentMarks (StudentID, ExamID, Score) VALUES (1, 2, 58)
                      INSERT INTO tblStudentMarks (StudentID, ExamID, Score) VALUES (1, 3, 75)
                      INSERT INTO tblStudentMarks (StudentID, ExamID, Score) VALUES (1, 4, 70)
                      INSERT INTO tblStudentMarks (StudentID, ExamID, Score) VALUES (1, 5, 60)
                      INSERT INTO tblStudentMarks (StudentID, ExamID, Score) VALUES (1, 6, 56)
                      INSERT INTO tblStudentMarks (StudentID, ExamID, Score) VALUES (1, 7, 70)
                      INSERT INTO tblStudentMarks (StudentID, ExamID, Score) VALUES (1, 8, 61)
                      INSERT INTO tblStudentMarks (StudentID, ExamID, Score) VALUES (2, 1, 53)
                      INSERT INTO tblStudentMarks (StudentID, ExamID, Score) VALUES (2, 2, 42)
                      INSERT INTO tblStudentMarks (StudentID, ExamID, Score) VALUES (2, 3, 68)
                      INSERT INTO tblStudentMarks (StudentID, ExamID, Score) VALUES (2, 4, 86)
                      INSERT INTO tblStudentMarks (StudentID, ExamID, Score) VALUES (2, 5, 69)
                      INSERT INTO tblStudentMarks (StudentID, ExamID, Score) VALUES (2, 6, 57)
                      INSERT INTO tblStudentMarks (StudentID, ExamID, Score) VALUES (2, 7, 66)
                      INSERT INTO tblStudentMarks (StudentID, ExamID, Score) VALUES (2, 8, 70)
                      INSERT INTO tblStudentMarks (StudentID, ExamID, Score) VALUES (3, 1, 74)
                      INSERT INTO tblStudentMarks (StudentID, ExamID, Score) VALUES (3, 2, 76)
                      INSERT INTO tblStudentMarks (StudentID, ExamID, Score) VALUES (3, 3, 81)
                      INSERT INTO tblStudentMarks (StudentID, ExamID, Score) VALUES (3, 4, 77)
                      INSERT INTO tblStudentMarks (StudentID, ExamID, Score) VALUES (3, 5, 96)
                      INSERT INTO tblStudentMarks (StudentID, ExamID, Score) VALUES (3, 6, 62)
                      INSERT INTO tblStudentMarks (StudentID, ExamID, Score) VALUES (3, 7, 59)
                      INSERT INTO tblStudentMarks (StudentID, ExamID, Score) VALUES (3, 8, 78)
                      INSERT INTO tblStudentMarks (StudentID, ExamID, Score) VALUES (4, 1, 85)
                      INSERT INTO tblStudentMarks (StudentID, ExamID, Score) VALUES (4, 2, 83)
                      INSERT INTO tblStudentMarks (StudentID, ExamID, Score) VALUES (4, 3, 97)
                      INSERT INTO tblStudentMarks (StudentID, ExamID, Score) VALUES (4, 4, 69)
                      INSERT INTO tblStudentMarks (StudentID, ExamID, Score) VALUES (4, 5, 74)
                      INSERT INTO tblStudentMarks (StudentID, ExamID, Score) VALUES (4, 6, 118)
                      INSERT INTO tblStudentMarks (StudentID, ExamID, Score) VALUES (4, 7, 103)
                      INSERT INTO tblStudentMarks (StudentID, ExamID, Score) VALUES (4, 8, 75)
                      INSERT INTO tblStudentMarks (StudentID, ExamID, Score) VALUES (5, 1, 101)
                      INSERT INTO tblStudentMarks (StudentID, ExamID, Score) VALUES (5, 2, 102)
                      INSERT INTO tblStudentMarks (StudentID, ExamID, Score) VALUES (5, 3, 91)
                      INSERT INTO tblStudentMarks (StudentID, ExamID, Score) VALUES (5, 4, 71)
                      INSERT INTO tblStudentMarks (StudentID, ExamID, Score) VALUES (5, 5, 92)
                      INSERT INTO tblStudentMarks (StudentID, ExamID, Score) VALUES (5, 6, 111)
                      INSERT INTO tblStudentMarks (StudentID, ExamID, Score) VALUES (5, 7, 94)
                      INSERT INTO tblStudentMarks (StudentID, ExamID, Score) VALUES (5, 8, 80)
                      INSERT INTO tblStudentMarks (StudentID, ExamID, Score) VALUES (6, 1, 78)
                      INSERT INTO tblStudentMarks (StudentID, ExamID, Score) VALUES (6, 2, 58)
                      INSERT INTO tblStudentMarks (StudentID, ExamID, Score) VALUES (6, 3, 53)
                      INSERT INTO tblStudentMarks (StudentID, ExamID, Score) VALUES (6, 4, 80)
                      INSERT INTO tblStudentMarks (StudentID, ExamID, Score) VALUES (6, 5, 113)
                      INSERT INTO tblStudentMarks (StudentID, ExamID, Score) VALUES (6, 6, 71)
                      INSERT INTO tblStudentMarks (StudentID, ExamID, Score) VALUES (6, 7, 35)
                      INSERT INTO tblStudentMarks (StudentID, ExamID, Score) VALUES (6, 8, 70)
                      INSERT INTO tblStudentMarks (StudentID, ExamID, Score) VALUES (7, 1, 72)
                      INSERT INTO tblStudentMarks (StudentID, ExamID, Score) VALUES (7, 2, 60)
                      INSERT INTO tblStudentMarks (StudentID, ExamID, Score) VALUES (7, 3, 70)
                      INSERT INTO tblStudentMarks (StudentID, ExamID, Score) VALUES (7, 4, 86)
                      INSERT INTO tblStudentMarks (StudentID, ExamID, Score) VALUES (7, 5, 96)
                      INSERT INTO tblStudentMarks (StudentID, ExamID, Score) VALUES (7, 6, 90)
                      INSERT INTO tblStudentMarks (StudentID, ExamID, Score) VALUES (7, 7, 89)
                      INSERT INTO tblStudentMarks (StudentID, ExamID, Score) VALUES (7, 8, 110)
                      INSERT INTO tblStudentMarks (StudentID, ExamID, Score) VALUES (8, 1, 64)
                      INSERT INTO tblStudentMarks (StudentID, ExamID, Score) VALUES (8, 2, 105)
                      INSERT INTO tblStudentMarks (StudentID, ExamID, Score) VALUES (8, 3, 85)
                      INSERT INTO tblStudentMarks (StudentID, ExamID, Score) VALUES (8, 4, 67)
                      INSERT INTO tblStudentMarks (StudentID, ExamID, Score) VALUES (8, 5, 46)
                      INSERT INTO tblStudentMarks (StudentID, ExamID, Score) VALUES (8, 6, 86)
                      INSERT INTO tblStudentMarks (StudentID, ExamID, Score) VALUES (8, 7, 82)
                      INSERT INTO tblStudentMarks (StudentID, ExamID, Score) VALUES (8, 8, 85)
                      INSERT INTO tblStudentMarks (StudentID, ExamID, Score) VALUES (9, 1, 74)
                      INSERT INTO tblStudentMarks (StudentID, ExamID, Score) VALUES (9, 2, 84)
                      INSERT INTO tblStudentMarks (StudentID, ExamID, Score) VALUES (9, 3, 75)
                      INSERT INTO tblStudentMarks (StudentID, ExamID, Score) VALUES (9, 4, 69)
                      INSERT INTO tblStudentMarks (StudentID, ExamID, Score) VALUES (9, 5, 72)
                      INSERT INTO tblStudentMarks (StudentID, ExamID, Score) VALUES (9, 6, 61)
                      INSERT INTO tblStudentMarks (StudentID, ExamID, Score) VALUES (9, 7, 88)
                      INSERT INTO tblStudentMarks (StudentID, ExamID, Score) VALUES (9, 8, 129)
                      INSERT INTO tblStudentMarks (StudentID, ExamID, Score) VALUES (10, 1, 89)
                      INSERT INTO tblStudentMarks (StudentID, ExamID, Score) VALUES (10, 2, 94)
                      INSERT INTO tblStudentMarks (StudentID, ExamID, Score) VALUES (10, 3, 101)
                      INSERT INTO tblStudentMarks (StudentID, ExamID, Score) VALUES (10, 4, 123)
                      INSERT INTO tblStudentMarks (StudentID, ExamID, Score) VALUES (10, 5, 97)
                      INSERT INTO tblStudentMarks (StudentID, ExamID, Score) VALUES (10, 6, 98)
                      INSERT INTO tblStudentMarks (StudentID, ExamID, Score) VALUES (10, 7, 106)
                      INSERT INTO tblStudentMarks (StudentID, ExamID, Score) VALUES (10, 8, 95)
                      
                      CREATE TABLE tblHarmMean (ID int IDENTITY(1, 1), Driver varchar(20), 
                          Speed decimal(7, 4))
                      
                      INSERT INTO tblHarmMean (Driver, Speed) VALUES ('A', 111)
                      INSERT INTO tblHarmMean (Driver, Speed) VALUES ('A', 79)
                      INSERT INTO tblHarmMean (Driver, Speed) VALUES ('A', 73)
                      INSERT INTO tblHarmMean (Driver, Speed) VALUES ('A', 62)
                      INSERT INTO tblHarmMean (Driver, Speed) VALUES ('A', 77)
                      INSERT INTO tblHarmMean (Driver, Speed) VALUES ('A', 94)
                      INSERT INTO tblHarmMean (Driver, Speed) VALUES ('A', 57)
                      INSERT INTO tblHarmMean (Driver, Speed) VALUES ('A', 70)
                      INSERT INTO tblHarmMean (Driver, Speed) VALUES ('A', 128)
                      INSERT INTO tblHarmMean (Driver, Speed) VALUES ('A', 100)
                      INSERT INTO tblHarmMean (Driver, Speed) VALUES ('B', 0)
                      INSERT INTO tblHarmMean (Driver, Speed) VALUES ('B', 116)
                      INSERT INTO tblHarmMean (Driver, Speed) VALUES ('B', 98)
                      INSERT INTO tblHarmMean (Driver, Speed) VALUES ('B', 90)
                      INSERT INTO tblHarmMean (Driver, Speed) VALUES ('B', 115)
                      INSERT INTO tblHarmMean (Driver, Speed) VALUES ('B', 118)
                      INSERT INTO tblHarmMean (Driver, Speed) VALUES ('B', 97)
                      INSERT INTO tblHarmMean (Driver, Speed) VALUES ('B', 111)
                      INSERT INTO tblHarmMean (Driver, Speed) VALUES ('B', 88)
                      INSERT INTO tblHarmMean (Driver, Speed) VALUES ('B', 129)
                      
                      CREATE TABLE tblHarmMeanWeighted (ID int IDENTITY(1, 1), Driver varchar(20), 
                          Distance decimal(10, 6), Speed decimal(9, 6))
                      
                      INSERT INTO tblHarmMeanWeighted (Driver, Distance, Speed) VALUES ('A', 700, 19)
                      INSERT INTO tblHarmMeanWeighted (Driver, Distance, Speed) VALUES ('A', 100, 90)
                      INSERT INTO tblHarmMeanWeighted (Driver, Distance, Speed) VALUES ('A', 100, 100)
                      INSERT INTO tblHarmMeanWeighted (Driver, Distance, Speed) VALUES ('A', 200, 75)
                      INSERT INTO tblHarmMeanWeighted (Driver, Distance, Speed) VALUES ('A', 400, 50)
                      INSERT INTO tblHarmMeanWeighted (Driver, Distance, Speed) VALUES ('A', 100, 73)
                      INSERT INTO tblHarmMeanWeighted (Driver, Distance, Speed) VALUES ('A', 300, 59)
                      INSERT INTO tblHarmMeanWeighted (Driver, Distance, Speed) VALUES ('A', 900, 31)
                      INSERT INTO tblHarmMeanWeighted (Driver, Distance, Speed) VALUES ('A', 1000, 80)
                      INSERT INTO tblHarmMeanWeighted (Driver, Distance, Speed) VALUES ('A', 400, 90)
                      INSERT INTO tblHarmMeanWeighted (Driver, Distance, Speed) VALUES ('B', 600, 75)
                      INSERT INTO tblHarmMeanWeighted (Driver, Distance, Speed) VALUES ('B', 700, 107)
                      INSERT INTO tblHarmMeanWeighted (Driver, Distance, Speed) VALUES ('B', 200, 63)
                      INSERT INTO tblHarmMeanWeighted (Driver, Distance, Speed) VALUES ('B', 600, 109)
                      INSERT INTO tblHarmMeanWeighted (Driver, Distance, Speed) VALUES ('B', 600, 97)
                      INSERT INTO tblHarmMeanWeighted (Driver, Distance, Speed) VALUES ('B', 100, 110)
                      INSERT INTO tblHarmMeanWeighted (Driver, Distance, Speed) VALUES ('B', 1000, 66)
                      INSERT INTO tblHarmMeanWeighted (Driver, Distance, Speed) VALUES ('B', 800, 64)
                      INSERT INTO tblHarmMeanWeighted (Driver, Distance, Speed) VALUES ('B', 800, 77)
                      INSERT INTO tblHarmMeanWeighted (Driver, Distance, Speed) VALUES ('B', 500, 78)

Open in new window



Weighted Average



A weighted average, sometimes called the weighted mean, is similar to the arithmetic mean.  However, while with the arithmetic mean all members of the data set are given equal weight, in a weighted average the various members of the data set may have different weights, and thus have unequal influence in the result.

To calculate a weighted average for a data set {x1, x2, x3, …, xn} with weights {w1, w2, w3, …, wn}, sum the products of the value of each data set member and its weight, and divide by the sum of the weights:

Weighted Average
None of the weights can be negative.  Further, while zero weights are allowed, there must be at least one non-zero weight.

Translating this into a generic SQL statement yields:

SELECT {group by columns if desired,} (CASE
                          WHEN MIN([WeightColumn]) >=0 AND Max([WeightColumn]) > 0 THEN
                              SUM([WeightColumn] * [ValueColumn])
                          ELSE NULL END) /
                          SUM([WeightColumn]) AS WtdAvg
                      FROM {tables}
                      {GROUP BY {group by columns}}

Open in new window


Note: The denominator in the aggregate expression does not need its own CASE expression to ward off the possibility of having the sum of weights being zero.  If that is the case, the numerator will already be null, and since any arithmetic operation in which one of the operands is null always results in null, SQL Server will not attempt to perform the division.

A weighted average is often used for computing an “average of averages”.  For example, consider the following sample results:

Sample  Count    Mean
                      ---------------------
                      A         100   425.7
                      B         200   398.6
                      C         300   379.1

Open in new window


If the three samples were combined, the overall mean would not be the average of the three sample means: since the three samples have an unequal number of members, the samples with more members should be given greater weight.  Thus, the weighted average is:

Sample  Count    Mean   Contribution
                      --------------------------------------------------
                      A         100   425.7   100 * 425.7 / 600 =  70.95
                      B         200   398.6   200 * 398.8 / 600 = 132.93
                      C         300   379.1   300 * 379.1 / 600 = 189.55
                      --------------------------------------------------
                      Result:                                     393.43

Open in new window


Example 1: Weighted Averages by Region and/or District

In this example, a company has conducted a series of customer surveys across its regions and districts.  The table tblWeightedAverage holds the results of each survey, noting the Region and District that participated, the number of customers surveyed (Weight), and the average score.  Our company now wishes to aggregate the survey results.

tblWeightedAverage
Since this is, in effect, an “average of averages” for which the number of data elements can vary, a weighted average is the only way to aggregate the results correctly.  Implementing the formula above yields the following SQL statements:

/* By Region */
                      
                      SELECT Region, (CASE
                          WHEN MIN([Weight]) >= 0 AND MAX([Weight]) > 0 THEN SUM([Weight] * [Value])
                          ELSE NULL END) / SUM([Weight]) AS WeightedAvg
                      FROM tblWeightedAverage
                      GROUP BY Region
                      ORDER BY Region;

Open in new window


/* By District */
                      
                      SELECT District, (CASE
                          WHEN MIN([Weight]) >= 0 AND MAX([Weight]) > 0 THEN SUM([Weight] * [Value])
                          ELSE NULL END) / SUM([Weight]) AS WeightedAvg
                      FROM tblWeightedAverage
                      GROUP BY District
                      ORDER BY District;

Open in new window


/* By Region and District */
                      
                      SELECT Region, District, (CASE
                          WHEN MIN([Weight]) >= 0 AND MAX([Weight]) > 0 THEN SUM([Weight] * [Value])
                          ELSE NULL END) / SUM([Weight]) AS WeightedAvg
                      FROM tblWeightedAverage
                      GROUP BY Region, District
                      ORDER BY Region, District;

Open in new window


Those queries return the following results:

Example 1 Results
Please note the following about this aggregation:
Instead of a straight aggregation, I used a conditional aggregation.  The conditions are set up to reject any data sets with negative weights, or data sets with no positive weights.  If a data set violates either or both of these conditions, the result is Null
If there is a Null weight for any member of a data set, that member has no impact on the resulting weighted average
If the value is null but the weight is not null, the aggregation is effectively treating that value as zero: that member does not contribute to the numerator, and yet its weight still contributes to the denominator

In this data set, I specifically set up a few items to test these conditions:
Region A, District 1 has an item with Weight = -2.  Since weights cannot be negative, this makes the results for that region/district Null
Region B, District 3 has an item with a Null weight.  That causes the corresponding value (97.4) to be ignored in the weighted average
Region C, District 5 has an item with a Null Value (but non-null Weight).  This item is included in the results, being treated as if the Value were zero
Both items for Region E, District 9 have zero Weights, thus causing the result to be Null


Example 2: Weighted Average for Course Grades

In this example, students’ grades for a particular course are a function of their grades on the four quizzes, two papers, midterm exam, and final exam.  Since the instructor places varying degrees of emphasis on these different items, each has a different weight:

Each of the four quizzes counts for 5% of the grade
Each of the two papers counts for 10% of the grade
The midterm exam counts for 20% of the grade
The final exam counts for the remaining 40% of the grade

Assuming that each item is graded on a 100-point scale, each student’s final grade will be a weighted average of his/her individual scores.

Grade tables
This example constitutes a special case of the weighted average: if the sum of weights is equal to 1, then the weighted average is simply the sum of the products of the individual values and their weights.  This simplifies the SQL statement, as it is no longer necessary to include the sum of the weights as the denominator in the aggregate expression.  (Division by one always returns the dividend.)

Thus, to determine our students’ grades, run the following query:

SELECT s.Lname, s.Fname, s.Mname, CASE
                          WHEN MIN(e.Weight) >= 0 AND MAX(e.Weight) > 0 THEN SUM(e.Weight * m.Score)
                          ELSE NULL END AS Grade
                      FROM tblExams e INNER JOIN
                          tblStudentMarks m ON e.ID = m.ExamID INNER JOIN
                          tblStudents s ON m.StudentID = s.ID
                      GROUP BY s.Lname, s.Fname, s.Mname;

Open in new window


That query returns the following results:

Example 2 Results
Please note the following:
As in Example 1, we test the weights to ensure that there are no negative weights, and that there is at least one positive weight
If any student has a “missing” grade in tblStudentMarks, the effect is the same as if s/he received a zero for that assignment
The SQL statement above assumes that the sum of the weights is in fact one.  If for any reason that is not actually the case, then the calculated weighted average will be incorrect


Harmonic Mean



The harmonic mean is mainly used for computing an average of rates or ratios.  Mathematically, the harmonic mean of a data set {x1, x2, x3, …, xn} is the reciprocal of the average of the reciprocals of the items in the data set:

Harmonic Mean
You cannot calculate the harmonic mean if the data set includes any zero or negative values, and thus any SQL statement used to calculate the harmonic mean must test to ensure that only positive numbers are included in the data set, and also must guard against the possibility of a division by zero error.

Translating this into a generic SQL statement yields:

SELECT {group by columns if desired,} CASE
                          WHEN MIN([ValueColumn]) > 0 THEN 1 / AVG(1 / (CASE [ValueColumn]
                              WHEN 0 THEN NULL ELSE [ValueColumn] END))
                          ELSE NULL END AS HarmMean
                      FROM {tables}
                      {GROUP BY {group by columns}}

Open in new window


Note: The denominator in the AVG expression must have its own CASE expression to escape any possible zero value in the ValueColumn.  If we leave out that embedded CASE expression and the data set does include a zero value, SQL Server will be unable to run the query and will return a division by zero error message.

As long as there is at least one pair of unequal values in the data set, the harmonic mean will always be less than the arithmetic mean.

A classic example for the harmonic mean involves averaging speeds over a given distance.  For example, if a person drives for one hour at 100 km/hr, and then for one hour at 50 km/hr, the average speed for the trip is the arithmetic mean of the two speeds, or 75 km/hr.

However, if instead we say that we drove for 100 km at 100 km/hr, and then drove for another 100 km at 50 km/hr, the average speed for the trip will be the harmonic mean:

Harmonic Mean = 1 / ([1/100 + 1/50] / 2) = 66.67 km/hr
                      
                      Validation:
                      
                      Time to travel 1st 100 km: 100km / 100km/hr = 1 hr
                      Time to travel 2nd 100 km: 100km / 50km/hr =  2 hr
                      Total travel time: 3 hr
                      Total distance: 200 km
                      Average speed = 200km / 3 hr = 66.67 km/hr

Open in new window


Example 3: Harmonic Mean

In this example, two drivers have both completed ten circuits of uniform length over a particular course.  For each circuit, the drivers’ average speeds are recorded in the table tblHarmMean.  We now wish to compute each driver’s overall average speed across all circuits.

tblHarmMean
In this scenario, the drivers’ average speeds will be the harmonic means of their speeds in each circuit.  The appropriate SQL statement is:

SELECT Driver, CASE
                          WHEN MIN([Speed]) > 0 THEN 1 / AVG(1 / (CASE [Speed]
                              WHEN 0 THEN NULL
                              ELSE [Speed] END))
                          ELSE NULL END AS HarmMean
                      FROM tblHarmMean
                      GROUP BY Driver;

Open in new window


That query returns the following results:

Example 3 Results
Driver B’s effective average speed cannot be calculated: the average speed for one of Driver B’s circuits is listed as zero.  The SQL statement above will return a Null result for any data set for which any item is less than or equal to zero, and it will ignore null values altogether.


Weighted Harmonic Mean



In the section above, we saw how to take an un-weighted harmonic mean.  You may also calculate a weighted harmonic mean.  For a data set with values {x1, x2, x3, …, xn} and weights {w1, w2, w3, …, wn}, the weighted harmonic mean is:

Weighted Harmonic Mean
As in the simple harmonic mean, the values must all be greater than zero.  Also, as with the weighted average, the weights cannot be negative, and there must be at least one non-zero weight.  Any SQL statement used for calculating the weighted harmonic mean must test for those conditions.

Translating this into a generic SQL statement yields:

SELECT {group by columns if desired,} CASE 
                          WHEN MIN([ValueColumn]) > 0 AND MIN([WeightColumn]) >=0 AND MAX([WeightColumn]) > 0 THEN
                              SUM([WeightColumn]) / SUM([WeightColumn] / [ValueColumn])
                          ELSE NULL END AS WtdHarmMean
                      FROM {tables}
                      {GROUP BY {group by columns}}

Open in new window


Note: As with the weighted average, we must test for having no negative weights, and at least one positive weight.  As with the unweighted harmonic mean, the denominator in the AVG expression must have its own CASE expression to escape any possible zero value in the ValueColumn, which would create a division by zero error.

For example, in finance, a common measure of the relative value of a company’s stock is the price to earnings ratio, or PE.  A relatively low PE indicates either a relatively under-valued stock or a company expected to have low or even negative future earnings growth, while a relatively high PE indicates either an over-valued stock or a company expected to have robust future earnings growth.

Consider the following companies, for which we want to determine the aggregate PE:

Company     PE Ratio
                      --------------------
                      A              12.50
                      B              14.29
                      C              20.00
                      D              50.00

Open in new window


If the companies have similar market values, then the “average” PE would be the harmonic means of the individual PE ratios.  However, if the companies’ market values are not similar, this would not be appropriate.  Suppose that the companies’ market values were as follows:

Company   Market Capitalization   PE Ratio
                      ------------------------------------------
                      A                $5,000,000,000      12.50
                      B                $2,000,000,000      14.29
                      C                  $200,000,000      20.00
                      D                   $20,000,000      50.00

Open in new window


Company A comprises almost 70% of the total market value of the four companies, and as such its PE should be accorded greater weight than the others’.  Thus, we should use the weighted harmonic mean.

Normalizing the weights by dividing each by 20,000,000 results in:

Company   Weight   PE Ratio
                      ---------------------------
                      A            250      12.50
                      B            100      14.29
                      C             10      20.00
                      D              1      50.00

Open in new window


Thus, the weighted harmonic mean is:

Wtd Harm Mean = (250 + 100 + 10 + 1) / (250/12.5 + 100/14.29 + 10/20 + 1/50) =
                                      361 / (20 + 7 + 0.5 + 0.02) = 13.12

Open in new window


Example 4: Weighted Harmonic Mean

This example is similar to Example 3, except that this time the circuits recorded for each driver are not necessarily all the same length.  For example, suppose that the circuit lengths and average speeds are as follows:

tblHarmMeanWeighted
In this case, we cannot simply take the harmonic means of each driver’s speeds.  Instead, we must find the weighted harmonic means, using the distance covered for each circuit as the weight.  To do this, use the following SQL statement:

SELECT Driver, CASE 
                          WHEN MIN([Speed]) > 0 AND MIN([Distance]) >=0 AND MAX([Distance]) > 0 THEN
                              SUM([Distance]) / SUM([Distance] / [Speed])
                          ELSE NULL END AS WtdHarmMean
                      FROM tblHarmMeanWeighted
                      GROUP BY Driver;

Open in new window


That query returns the following results:

Example 4 Results

To Be Continued…


Please be sure to continue on to Part 2 of this article, which will extend the discussion to include the following other types of means, and how to calculate them using Microsoft SQL Server:



=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
If you liked this article and want to see more from this author,  please click here.

If you found this article helpful, please click the Yes button near the:

      Was this article helpful?

label that is just below and to the right of this text.   Thanks!
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
5
14,287 Views
Patrick Matthews
CERTIFIED EXPERT

Comments (2)

Kevin CrossChief Technology Officer
CERTIFIED EXPERT
Most Valuable Expert 2011

Commented:
Brilliance as usual, Patrick.

Thank you very much sharing!  You have a BIG Yes vote from me.

Kevin
Henrik Staun PoulsenDatabase developer
CERTIFIED EXPERT

Commented:
I would prefer to use this for the Harmonic Avg:
SELECT {group by columns if desired,} CASE
    WHEN MIN([ValueColumn]) > 0 THEN 1 / AVG(1 / (nullif([ValueColumn],0) ))
    ELSE NULL END AS HarmMean
FROM {tables}
{GROUP BY {group by columns}}

Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.