# Calculating MEAN, MODE and MEDIAN

### Question

• I need to calculate the MEAN, MODE and MEDIAN. I can easily calculate the mean which is the average, but im falling short on calculating the MODE and MEDIAN... please can i have some assistance with this...

below is a sample of the dataset im using... unfortunately the dataset contains over 50k records, but i've only included a few for the example, see below.

```Declare @Temp Table
(class varchar(20)
, month varchar(20)
, monthorder varchar(20)
, duration int)

Insert into @Temp Values('301', 'April', '4', '1')
Insert into @Temp Values('321', 'April', '4', '9')
Insert into @Temp Values('318', 'April', '4', '2')
Insert into @Temp Values('994', 'April', '4', '7')
Insert into @Temp Values('232', 'April', '4', '9')
Insert into @Temp Values('128', 'April', '4', '3')
Insert into @Temp Values('138', 'April', '4', '0')
Insert into @Temp Values('301', 'April', '4', '0')
Insert into @Temp Values('321', 'April', '4', '3')
Insert into @Temp Values('318', 'April', '4', '9')
Insert into @Temp Values('994', 'April', '4', '7')
Insert into @Temp Values('232', 'April', '4', '2')
Insert into @Temp Values('128', 'April', '4', '9')
Insert into @Temp Values('138', 'April', '4', '1')
Insert into @Temp Values('301', 'May', '5', '7')
Insert into @Temp Values('321', 'May', '5', '6')
Insert into @Temp Values('318', 'May', '5', '6')
Insert into @Temp Values('994', 'May', '5', '3')
Insert into @Temp Values('232', 'May', '5', '9')
Insert into @Temp Values('128', 'May', '5', '1')
Insert into @Temp Values('138', 'May', '5', '2')
Insert into @Temp Values('301', 'May', '5', '2')
Insert into @Temp Values('321', 'May', '5', '1')
Insert into @Temp Values('318', 'May', '5', '9')
Insert into @Temp Values('994', 'May', '5', '3')
Insert into @Temp Values('232', 'May', '5', '6')
Insert into @Temp Values('128', 'May', '5', '6')
Insert into @Temp Values('138', 'May', '5', '7')
Insert into @Temp Values('301', 'June', '6', '4')
Insert into @Temp Values('321', 'June', '6', '3')
Insert into @Temp Values('301', 'June', '6', '3')
Insert into @Temp Values('321', 'June', '6', '4')
select
class
, month
, monthorder
, avg(duration) mean_duration
from @temp
group by class, month, monthorder```

Wednesday, September 19, 2012 2:03 PM

• Median is

```;With cte As
(Select class, month, monthorder, duration,
Count(*) Over (Partition By class, month, monthorder) As TotalCount,
Row_Number() Over (Partition By class, month, monthorder Order By duration) As rn
From @temp)
Select class, month, monthorder,
Avg(Case When Abs(rn - (TotalCount + 1.0) / 2) < .7 Then Cast(duration As decimal(9,2))End) As MedianDuration
From cte
Group By class, month, monthorder;```

Mode is

```;With cte As
(Select class, month, monthorder, duration,
Count(*) Over (Partition By class, month, monthorder) As TotalCount,
Count(*) Over (Partition By class, month, monthorder, duration) As DurationCount
From @temp),
cteOrderedCounts As
(Select class, month, monthorder, duration,
Rank() Over (Partition By class, month, monthorder Order By DurationCount Desc) As ModeNumber
From cte)

Select Distinct class, month, monthorder, duration
From cteOrderedCounts
Where ModeNumber = 1;```

Tom

• Marked as answer by Thursday, September 20, 2012 12:39 PM
Wednesday, September 19, 2012 3:04 PM

### All replies

• Wednesday, September 19, 2012 2:07 PM
• thanks for the link, just been reading that earlier but it doesn't allow me to add more categories (fields/groups) to the median query and the mode.

because i need the data grouped by class, month and monthorder.

Wednesday, September 19, 2012 2:12 PM
• Median is

```;With cte As
(Select class, month, monthorder, duration,
Count(*) Over (Partition By class, month, monthorder) As TotalCount,
Row_Number() Over (Partition By class, month, monthorder Order By duration) As rn
From @temp)
Select class, month, monthorder,
Avg(Case When Abs(rn - (TotalCount + 1.0) / 2) < .7 Then Cast(duration As decimal(9,2))End) As MedianDuration
From cte
Group By class, month, monthorder;```

Mode is

```;With cte As
(Select class, month, monthorder, duration,
Count(*) Over (Partition By class, month, monthorder) As TotalCount,
Count(*) Over (Partition By class, month, monthorder, duration) As DurationCount
From @temp),
cteOrderedCounts As
(Select class, month, monthorder, duration,
Rank() Over (Partition By class, month, monthorder Order By DurationCount Desc) As ModeNumber
From cte)

Select Distinct class, month, monthorder, duration
From cteOrderedCounts
Where ModeNumber = 1;```

Tom

• Marked as answer by Thursday, September 20, 2012 12:39 PM
Wednesday, September 19, 2012 3:04 PM
• See more recent blog on this exact topic on that site

# It's Hard To Be "Average": Mean, Median, and Mode in SQL Server

For every expert, there is an equal and opposite expert. - Becker's Law

My blog

Wednesday, September 19, 2012 3:07 PM
• Hi Tom Cooper....

thanks for the help... im a little stuck in trying to compile the Median query correctly. I get the following error message.

Msg 102, Level 15, State 1, Line 4

Incorrect syntax near 'month'.

```;With cte As
(Select pas_code, datename(month, transferdate_dttm) month, datepart(month, transferdate_dttm) monthorder, duration,
Count(*) Over (Partition By pas_code, datename(month, transferdate_dttm) month,  datepart(month, transferdate_dttm) monthorder) As TotalCount,
Row_Number() Over (Partition By pas_code, datename(month, transferdate_dttm) month, datepart(month, transferdate_dttm) monthorder Order By duration) As rn
From #temp)
Select pas_code, month, monthorder,
Avg(Case When Abs(rn - (TotalCount + 1.0) / 2) < .7 Then Cast(duration As decimal(9,2))End) As MedianDuration
From cte
Group By pas_code, month, monthorder;```

Thursday, September 20, 2012 7:49 AM
• and for the mode query, the column labelled duration, is that the mode value?

thanks

Thursday, September 20, 2012 8:02 AM
• Remove column aliases here

``` Count(*) Over (Partition By pas_code, datename(month, transferdate_dttm) month,  datepart(month, transferdate_dttm) monthorder) As TotalCount,
Row_Number() Over (Partition By pas_code, datename(month, transferdate_dttm) month, datepart(month, transferdate_dttm) monthorder Order By duration) As rn```

instead it should be

``` Count(*) Over (Partition By pas_code, datename(month, transferdate_dttm)) As TotalCount,
Row_Number() Over (Partition By pas_code, datename(month, transferdate_dttm) Order By duration) As rn```

Also no need to partition by month name and month value as it's the same partitioning.

For every expert, there is an equal and opposite expert. - Becker's Law

My blog

Thursday, September 20, 2012 12:05 PM