Calculating the Median in SQL Query

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

General SQL Server Forums

New to SQL Server Programming

Calculating the Median in SQL Query

Author

Topic

archana23
Yak Posting Veteran

89 Posts

Posted - 2013-12-05 : 13:55:03

Hi,

I have table which has columns with Nurse , PatientName, ArrivalDate,DepartDate,DifferenceTime(Min)

so i am calculating median from this table as

SET @Median =
(
SELECT
(
(
SELECT
MAX(DifferenceTime)
FROM
(
SELECT
TOP 50 PERCENT DifferenceTime
FROM
PatTable
ORDER BY
DifferenceTime
)
AS BottomHalf
)
+
(
SELECT
MIN(DifferenceTime)
FROM
(
SELECT
TOP 50 PERCENT DifferenceTime
FROM
PatTable
ORDER BY
DifferenceTime DESC
)
AS TopHalf
)
)
/ 2
)

I am getting median from above expression.

but i need to calculate median per nurse

How can i do this?

can any one please help me on this?

Thanks,

Archana

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-12-05 : 14:01:28

Which version are you using? If its 2012, you can use PERCENTILE_DISC function for this

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

archana23
Yak Posting Veteran

89 Posts

Posted - 2013-12-05 : 14:15:51

Thanks visakh16 for your reply.

We are using SQL server 2008 version..

Archana

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2013-12-05 : 14:47:15

try this and see if it is an improvement or not. It will work with 2008:


;with PatTable (nurse, DifferenceTime)
as
(
       select 1, 1 union all
       select 1, 60 union all
       select 1, 61 union all
       select 2, 10 union all
       select 2, 50 union all
       select 2, 60 union all
       select 2, 61
)

select nurse
       ,avg(DifferenceTime) as median 
from   (
       select row_number() over (partition by nurse order by DifferenceTime asc) as asc_srt
	       , row_number() over (partition by nurse order by DifferenceTime desc) as desc_srt
	       , DifferenceTime
              , nurse
	from   PatTable
       ) as t
where asc_srt - desc_srt between -1 and 1
group by nurse

OUTPUT:
nurse       median
----------- -----------
1           60
2           55

EDIT:
added some sample data with output

Be One with the Optimizer
TG

archana23
Yak Posting Veteran

89 Posts

Posted - 2013-12-05 : 15:35:22

Its working.

Thanks TG.

Archana

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2013-12-05 : 16:20:09

you're welcome - thanks for reporting back.

Be One with the Optimizer
TG

archana23
Yak Posting Veteran

89 Posts

Posted - 2013-12-05 : 16:24:39

Hi TG,
I have one more question ,I need show my result as like this

Nurse PatientCount AvgOfDifferenceTime MedianOfDifferenceTime

Tina 2 112.20 112.2

Deena 3 162.80 150

So for this first 3 fields i am getting from my query as like this

select Nurse , COUNT(DISTINCT PatientName) As PatientCount,
convert(numeric(6,2),SUM(DifferenceTime) * 1.0/COUNT(DISTINCT PatientName)) AS AvgOfDifferenceTime

and i am getting last field called MedianOfDifferenceTime from your above query

select nurse
,avg(DifferenceTime) as median
from (
select row_number() over (partition by nurse order by DifferenceTime asc) as asc_srt
, row_number() over (partition by nurse order by DifferenceTime desc) as desc_srt
, DifferenceTime
, nurse
from PatTable
) as t
where asc_srt - desc_srt between -1 and 1
group by nurse

Can you please combine these 2 queries to get the result as above??

Sorry to ask you simple questions but i am new to this SQL server :(

Thank you

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2013-12-05 : 16:44:48

here's one way which assumes you have a column called [patientid]


;with PatTable (nurse, DifferenceTime, patientid)
as
(
       select 1, 1, 10 union all
       select 1, 60, 11 union all
       select 1, 61, 12 union all
       select 2, 10, 13 union all
       select 2, 50, 13 union all
       select 2, 60, 14 union all
       select 2, 61, 15
)

select Nurse 
       ,PatientCount = count(distinct patientid) --if a patient can be in multiple rows
       ,PatientCount = count(*)                  --if each row is one different patient
       ,TotalDifferenceTime = sum(DifferenceTime) 
       ,AvgOfDifferenceTime = avg(DifferenceTime) 
       ,MedianOfDifferenceTime = avg(case when asc_srt - desc_srt between -1 and 1 then DifferenceTime else null end)
from   (
       select row_number() over (partition by nurse order by DifferenceTime asc) as asc_srt
	       , row_number() over (partition by nurse order by DifferenceTime desc) as desc_srt
	       , DifferenceTime
              , nurse
              , patientid
	from   PatTable
       ) as t
group by nurse

OUTPUT:
Nurse       PatientCount PatientCount TotalDifferenceTime AvgOfDifferenceTime MedianOfDifferenceTime
----------- ------------ ------------ ------------------- ------------------- ----------------------
1           3            3            122                 40                  60
2           3            4            181                 45                  55

EDIT:
added output (again)

Be One with the Optimizer
TG

archana23
Yak Posting Veteran

89 Posts

Posted - 2013-12-05 : 17:04:40

Perfect !!!! Thank you so much TG. Appreciated for your help...

Archana

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-12-06 : 04:45:25

Beware that double ROW_NUMBER() can return the wrong result!
See http://www.sqltopia.com/?page_id=62

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources