how to calculate median value


  • --DECLARE @Count AS INT


    --select @count

    --WITH MyResults(RowNo,Admins) AS


    -- SELECT RowNo,admins FROM

    -- (SELECT ROW_NUMBER() OVER (ORDER BY admins) AS RowNo,admins FROM #SUMMARY) AS Foo

    Thursday, May 31, 2012 9:21 AM

All replies

  • More details please. also refer the below link.

    ESHANI. Please click "Mark As Answer" if a post solves your problem or "Vote As Helpful" if a post has been useful to you

    Thursday, May 31, 2012 9:27 AM
  • This is a very interesting topic.

    I think without understading what is median then you can't do anything. I believe that the median value for a group of data, it should be the value equal or above the middle value by ordered ascendingly.

    from my very simple test data I just deliberately give it 10 rows per group, and order by (YOu don't need to order I just for simple view ppurpose) MyValue, I have a rownum in the test data set (You don't need to have the rownum as it will all covered in the code). You can see what value will be the median. My solution is:

    Declare @MyTable Table (ID varchar(10), MyValue int, RowNum int)
    Insert into @MyTable
    Select 'A', 4, 1 Union All
    Select 'A', 15, 2 Union All
    Select 'A', 15, 3 Union All
    Select 'A', 23, 4 Union All
    Select 'A', 24, 5 Union All ---The median value for A should >= 24
    Select 'A', 27, 6 Union All --And the median value for A should <=27
    Select 'A', 34, 7 Union All
    Select 'A', 40, 8 Union All
    Select 'A', 42, 9 Union All
    Select 'A', 57, 10 Union All
    Select 'B', 0, 1 Union All
    Select 'B', 1, 2 Union All
    Select 'B', 1, 3 Union All
    Select 'B', 12, 4 Union All
    Select 'B', 13, 5 Union All ---The median value for B should >= 13
    Select 'B', 14, 6 Union All --And the median value for B should <=14
    Select 'B', 41, 7 Union All
    Select 'B', 48, 8 Union All
    Select 'B', 48, 9 Union All
    Select 'B', 57, 10
    --Select *
    --From @MyTable
    ;With A
    SElect ID, MyValue
    		, ROW_NUMBER() Over(PARTITION By ID Order by MyValue) As MyPosition
    		, COUNT(*) Over(PARTITION  by ID) As Total_Row_Group
    From @MyTable
    Select ID, AVG(1.0 * MyValue) AS Median
    From	A
    Where	MyPosition = (Total_Row_Group + 1) / 2
    		Or MyPosition = (Total_Row_Group + 2) / 2
    Group By ID		

    Thursday, May 31, 2012 9:48 AM
  • try this:

    declare @x table (Admins int)
    insert @x
    select 3 union all
    select 1 union all
    select 8 union all
    select 6 union all
    select 12 union all
    select 5 union all
    select 15
    select * from @x order by admins
    ;with T as (
       select rn = row_number() over (order by admins), Cnt = 1 + count(*) over (partition by 1), Admins
       from @x
    select Median = avg(Admins * 1.0) from T
    where RN between floor(Cnt / 2.0) and round(Cnt / 2.0, 0)

    The simpler the solution the stronger it is
    If this post answers you, please mark it as answer..
    If this post is useful, please vote it as useful..

    • Edited by Sali SQL Thursday, May 31, 2012 12:30 PM
    Thursday, May 31, 2012 9:49 AM
  • Check this good blog post by Peter Larrson

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

    My blog

    Thursday, May 31, 2012 7:52 PM
  • It would be nice if you had statistics on the differentiating column and you could query the statistics to get a "jet assisted" answer for this.
    Thursday, May 31, 2012 8:06 PM