Asked by:
how to calculate median value
Question
All replies

More details please. also refer the below link.
http://stackoverflow.com/questions/1342898/functiontocalculatemedianinsqlserver
ESHANI. Please click "Mark As Answer" if a post solves your problem or "Vote As Helpful" if a post has been useful to you

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 AS ( 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
 Proposed as answer by Satheesh Variath Thursday, May 31, 2012 10:16 AM
 Edited by Steven Wang  Shangzhou Thursday, May 31, 2012 10:38 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

Check this good blog post by Peter Larrson
http://www.sqltopia.com/?page_id=62
For every expert, there is an equal and opposite expert.  Becker's Law
My blog 