none
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

Answers

  • 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 Sam233 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 Sam233 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