# how to calculate median value

### Question

• --DECLARE @Count AS INT

--SELECT @Count = COUNT(*) FROM #SUMMARY;

--select @count

--(

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

Thursday, May 31, 2012 9:21 AM

### All replies

http://stackoverflow.com/questions/1342898/function-to-calculate-median-in-sql-server

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
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		```

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 is useful, please vote it as useful..

• Edited by Thursday, May 31, 2012 12:30 PM
Thursday, May 31, 2012 9:49 AM
• 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

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