XML Feeds
Calculating Median and Mode with SQL Server can be frustrating for some developers, but it doesn't have to be. Often times, inexperienced developers will attempt to write this with procedural programming practices, but set based methods do exist.
Before showing you methods to calculate these values, it’s probably best to explain what they are.
Mean is another name for average. SQL Server has a built-in function to calculate this value.
Data ---- 1 2 5 5 5 6 6 6 7 9 10
To calculate the average, sum the data and divide by the number of rows. In this case, 1 + 2 + 5 + 5 + 5 + 6 + 6 + 6 + 7 + 9 + 10 = 62. 62/11 = 5.636363
Median represents the 'middle' value. To calculate the median by hand, you sort the values and return the value that appears in the middle of the list. If there is an add number of items, there will be exactly one value in the middle of the list. If there is an even number of items, you average the 2 values that are closest to the middle.
Data
1
2
5
5
5
6
6
6
7
9
10
Since there is an odd number of rows, the row appearing in the middle of the list contains your median value.
Data
1
2
5
5
5
6
6
6
7
9
Now, there is an even number of rows. The median for this data set is (5 + 6)/2 = 5.5. Simply take the average of the 2 values appearing in the middle of the data set.
MODE
The mode for a data set is the item(s) that appear most frequently. To calculate this by hand, you write a distinct list of values and count the number of times a value appears. The value the appears the most is your mode.
Data Frequency
---- ---------
1 1
2 1
5 3
6 3
7 1
9 1
10 1
This data set is considered to be Bi-Modal because there are 2 values with the same frequency. With this data set, the modes are 5 and 6.
For demonstration purposes, I will create a table variable and populate it with data. All code will be based on this table variable. The data type for the data column will be Decimal(10,5). If we used an integer column, then we would need to concern ourselves with integer math issues, which is not the focus of this blog.
AVERAGE
As I stated earlier, SQL Server has a built-in function for calculating the average. The Avg function will ignore rows with NULL. So the average of 1, 2, NULL is 1.5 because the sum of the data is 3 and there are 2 rows that are not NULL. 3/2 = 1.5.
- DECLARE @Temp TABLE(Id INT IDENTITY(1,1), DATA DECIMAL(10,5))
- INSERT INTO @Temp VALUES(1)
- INSERT INTO @Temp VALUES(2)
- INSERT INTO @Temp VALUES(5)
- INSERT INTO @Temp VALUES(5)
- INSERT INTO @Temp VALUES(5)
- INSERT INTO @Temp VALUES(6)
- INSERT INTO @Temp VALUES(6)
- INSERT INTO @Temp VALUES(6)
- INSERT INTO @Temp VALUES(7)
- INSERT INTO @Temp VALUES(9)
- INSERT INTO @Temp VALUES(10)
- INSERT INTO @Temp VALUES(NULL)
- SELECT AVG(DATA)
- FROM @Temp
- -- 5.636363
MEDIAN
To calculate the median, we will select the last value in the top 50 percent of rows, and the first value in the bottom 50 percent (all while ignoring NULL values).
To get the last value in the top 50 percent of rows….
Select Top 1 Data From ( Select Top 50 Percent Data From @Temp Where Data Is NOT NULL Order By Data ) As A Order By Data DESC
To get the first value in the last 50 percent of rows…
Select Top 1 Data From ( Select Top 50 Percent Data From @Temp Where Data Is NOT NULL Order By Data DESC ) As A Order By Data Asc
Putting it all together:
- DECLARE @Temp TABLE(Id INT IDENTITY(1,1), DATA DECIMAL(10,5))
- INSERT INTO @Temp VALUES(1)
- INSERT INTO @Temp VALUES(2)
- INSERT INTO @Temp VALUES(5)
- INSERT INTO @Temp VALUES(5)
- INSERT INTO @Temp VALUES(5)
- INSERT INTO @Temp VALUES(6)
- INSERT INTO @Temp VALUES(6)
- INSERT INTO @Temp VALUES(6)
- INSERT INTO @Temp VALUES(7)
- INSERT INTO @Temp VALUES(9)
- INSERT INTO @Temp VALUES(10)
- INSERT INTO @Temp VALUES(NULL)
- SELECT ((
- SELECT TOP 1 DATA
- FROM (
- SELECT TOP 50 PERCENT DATA
- FROM @Temp
- WHERE DATA IS NOT NULL
- ORDER BY DATA
- ) AS A
- ORDER BY DATA DESC) +
- (
- SELECT TOP 1 DATA
- FROM (
- SELECT TOP 50 PERCENT DATA
- FROM @Temp
- WHERE DATA IS NOT NULL
- ORDER BY DATA DESC
- ) AS A
- ORDER BY DATA ASC)) / 2
- -- 6
MODE
To Calculate the mode with sql server, we first need to get the counts for each value in the set. Then, we need to filter the data so that values equal to the count are returned.
- DECLARE @Temp TABLE(Id INT IDENTITY(1,1), DATA DECIMAL(10,5))
- INSERT INTO @Temp VALUES(1)
- INSERT INTO @Temp VALUES(2)
- INSERT INTO @Temp VALUES(5)
- INSERT INTO @Temp VALUES(5)
- INSERT INTO @Temp VALUES(5)
- INSERT INTO @Temp VALUES(6)
- INSERT INTO @Temp VALUES(6)
- INSERT INTO @Temp VALUES(6)
- INSERT INTO @Temp VALUES(7)
- INSERT INTO @Temp VALUES(9)
- INSERT INTO @Temp VALUES(10)
- INSERT INTO @Temp VALUES(NULL)
- SELECT DATA
- FROM @Temp
- WHERE DATA IS Not NULL
- GROUP BY DATA
- HAVING COUNT(*) = (
- SELECT MAX(CountRows)
- FROM (
- SELECT DATA, COUNT(*) AS CountRows
- FROM @Temp
- WHERE DATA IS Not NULL
- GROUP BY DATA
- ) AS A)
As you can see, there are set based methods for calculating all of these values, which can be many times faster than calculating these values in a cursor.
