Median Calc ??? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Median Calc ???

Author

Topic

sqlnovice1
Starting Member

34 Posts

Posted - 2011-09-12 : 11:28:25

Does anyone know how to calculate the median of a column of figures.

I have a column which is derived from a datediff and result shown as minutes in a view.

I know via Excel that the MEDIAN for the following is 12.5. HOw would I express this in SQL

Thanks in advance for any help

CREATE TABLE PS_TestForOnline
(
number NVARCHAR (20),

);

INSERT INTO PS_TestForOnline
VALUES('1' );
INSERT INTO PS_TestForOnline
VALUES('5' );
INSERT INTO PS_TestForOnline
VALUES('10' );
INSERT INTO PS_TestForOnline
VALUES('15' );
INSERT INTO PS_TestForOnline
VALUES('20' );
INSERT INTO PS_TestForOnline
VALUES('999' );

SELECT * FROM PS_TestForOnline

DROP TABLE PS_TestForOnline

Paul

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-09-12 : 11:50:43

http://weblogs.sqlteam.com/peterl/archive/2009/09/16/Median-and-weighted-median.aspx

sqlnovice1
Starting Member

34 Posts

Posted - 2011-09-12 : 14:57:39

Many Thanks

sqlnovice1
Starting Member

34 Posts

Posted - 2011-09-13 : 07:20:19

Hi,

Having tried the sugestion in the link provided I have got the answer. Using the following.

How would i adjust it so I couuld group on an other field ????

Thanks

SELECT AVG(1.0E * Arrival_To_Treatment)
FROM (SELECT Arrival_To_Treatment,
ROW_NUMBER() OVER (ORDER BY Arrival_To_Treatment DESC) AS a,
ROW_NUMBER() OVER (ORDER BY Arrival_To_Treatment) AS b
FROM inf.vw_AE_PSNEW_Main_Attendance_View
where (Arrival_To_Treatment is not null and Arrival_To_Treatment >= 0)
and arrival >= '2011-08-01 00:00'
) AS d
WHERE b - a BETWEEN -1 AND 1

sqlnovice1
Starting Member

34 Posts

Posted - 2011-09-13 : 15:59:15

I'm trying to work with the solution in this Topic and group the Median by Month and eventually Year. I have the follwoing script which runs OK but doesnt seem to give me the correct results.

I am getting the median of the total for each of the groups rather than getting the median for each Month.

Can anyone tell me where I'm going wrong?

script

select Att_Year, Att_Month,

/* median calc*/
(SELECT AVG(1.0E * Arrival_To_Treatment) as median
FROM (SELECT Arrival_To_Treatment,
ROW_NUMBER() OVER (ORDER BY Arrival_To_Treatment DESC) AS a,
ROW_NUMBER() OVER (ORDER BY Arrival_To_Treatment) AS b
FROM inf.vw_AE_PSNEW_Main_Attendance_View

) AS d
WHERE b - a BETWEEN -1 AND 1) as Median

FROM inf.vw_AE_PSNEW_Main_Attendance_View

where (Arrival_To_Treatment is not null and Arrival_To_Treatment >= 0)
and arrival >= '2011-01-01 00:00'

group by Att_Year, Att_Month

RESULTS ????????

2011 APR 103
2011 AUG 103
2011 FEB 103
2011 JAN 103
2011 JUL 103
2011 JUN 103
2011 MAR 103
2011 MAY 103
2011 SEP 103

Paul

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-09-14 : 00:42:40

You are using the wrong query. You are using the one I demonstrated will give wrong result under certain circumstances.
Use my code at the bottom of the page.

N 56°04'39.26"
E 12°55'05.63"

Julien.Crawford
Starting Member

21 Posts

Posted - 2011-09-14 : 20:05:05

I'm a little confused the answers here. (I haven't investigated why they would work - but I will look).

The median is the MIDDLE number, this is not difficult for a human. If you wanted to do this you would need to sort the list and select the middle item.
So that's all I did.

It would be nice to not have to sort it all, and I'm thinking about that still but here is my rather simple (at the cost of a sort) example.

Julien.



drop table test_data
go
create table test_data ( n int )
go
insert into test_data select 10
go
insert into test_data select 10
go
insert into test_data select 100
go
insert into test_data select 10
go
insert into test_data select 20
go
insert into test_data select 100
go
insert into test_data select 100
go

declare @n int
select @n = (count(*)/2) + 1 from test_data

set rowcount @n

select @n = n from test_data order by n
select @n MEDIAN

set rowcount 0

sqlnovice1
Starting Member

34 Posts

Posted - 2011-09-16 : 09:17:22

OK,

Using SWEPESO's script (the non weighted one)

Added in a bit more sample data.

Have fiddled with script a bit to try and get the results to group by the month. Perhaps it is my in-experience with SQL but, i cannot seem to get it to group by month.

Any ideas ??? Thanks in advance

Revised Sample data and current script

CREATE TABLE #data (number INT, Month_Name nvarchar (10))
INSERT INTO #data
SELECT 15 as number, 'jan' as Month_Name union all
SELECT 26 as number, 'jan' as Month_Name union all
SELECT 47 as number, 'jan' as Month_Name union all
SELECT 25 as number, 'jan' as Month_Name union all
SELECT 15 as number, 'jan' as Month_Name union all
SELECT 20 as number, 'jan' as Month_Name union all
SELECT 22 as number, 'jan' as Month_Name union all
SELECT 40 as number, 'jan' as Month_Name union all
SELECT 98 as number, 'mar' as Month_Name union all
SELECT 15 as number, 'mar' as Month_Name union all
SELECT 48 as number, 'mar' as Month_Name union all
SELECT 75 as number, 'mar' as Month_Name union all
SELECT 25 as number, 'mar' as Month_Name union all
SELECT 40 as number, 'mar' as Month_Name union all
SELECT 44 as number, 'mar' as Month_Name union all
SELECT 40 as number, 'mar' as Month_Name union all
SELECT 5 as number, 'feb' as Month_Name union all
SELECT 2 as number, 'feb' as Month_Name union all
SELECT 3 as number, 'feb' as Month_Name union all
SELECT 4 as number, 'feb' as Month_Name union all
SELECT 5 as number, 'feb' as Month_Name union all
SELECT 2 as number, 'feb' as Month_Name union all
SELECT 3 as number, 'feb' as Month_Name union all
SELECT 4 as number, 'feb' as Month_Name union all
SELECT 7 as number, 'feb' as Month_Name

select * from #data

--8888888888888888888888888888888888

SELECT AVG(1.0E * number) ,Month_Name as mine
FROM (
SELECT number,Month_Name,
2 * ROW_NUMBER() OVER (ORDER BY number) - COUNT(*) OVER () AS y
FROM #data
)AS d
WHERE y BETWEEN 0 AND 2
group by Month_Name

--8888888888888888888888888888888888

drop table #data

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-09-16 : 10:24:33

[code]SELECT AVG(1E * Number),
Month_Name AS Mine
FROM (
SELECT Number,
Month_Name,
2 * ROW_NUMBER() OVER (PARTITION BY Month_Name ORDER BY Number) - COUNT(*) OVER (PARTITION BY Month_Name) AS y
FROM #Data
) AS d
WHERE y BETWEEN 0 AND 2
GROUP BY Month_Name[/code]

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources