/* 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)
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}}
Sample Count Mean
---------------------
A 100 425.7
B 200 398.6
C 300 379.1
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
/* 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;
/* 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;
/* 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;
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;
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}}
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
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;
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}}
Company PE Ratio
--------------------
A 12.50
B 14.29
C 20.00
D 50.00
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
Company Weight PE Ratio
---------------------------
A 250 12.50
B 100 14.29
C 10 20.00
D 1 50.00
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
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;
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.
Comments (2)
Commented:
Thank you very much sharing! You have a BIG Yes vote from me.
Kevin
Commented:
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}}