How do I calculate the Median?

Question

How do I calculate the Median?

Nuts

SSCrazy

Points: 2547
More actions
June 11, 2009 at 10:03 pm

#132872

Hi Guys
I have got a range of numbers which are actually the number of tests done by the doctors over a period of time.
Now I need to find the median value of these tests
Here is my sample data
Region District Doctor_Id Tests
1 a A1 12
2 b A2 15
3 b Aq 16
4 c B4 100
.
.
.
Now I need to find 3 medians:
1) for the whole Region
2) for the different districts
3) for individual doctors
Your help will be much appreciated
Thanks

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Bruce W Cassidy SSChampion Points: 12668 More actions · Answer 1

[font="Verdana"]Statistical medians aren't as easy to calculate as you might think.

You might want to start by having a look at Joe Celko's excellent article on the topic: Median Workbench[/url]. There's some good code examples in there.

If all you really want is the average, then take a look at AVG() in SQL Server Books Online.

[/font]

Nuts SSCrazy Points: 2547 More actions · Answer 2

No I dont want the average. I know how to calculate that.

I want to find out the median value.

Thanks

Mark Cowne One Orange Chip Points: 26952 More actions · Answer 3

See this thread

http://www.sqlservercentral.com/Forums/FindPost693820.aspx

For example

WITH CTE AS (

SELECT Doctor_Id,

Tests,

ROW_NUMBER() OVER(PARTITION BY Doctor_Id ORDER BY Tests ASC) AS rn,

COUNT(Tests) OVER(PARTITION BY Doctor_Id) AS cn

FROM MyTable

WHERE Tests IS NOT NULL

)

SELECT Doctor_Id,

MIN(Tests) AS [Minimum],

MAX(Tests) AS [Maximum],

AVG(Tests) AS [Average],

AVG(CASE WHEN 2 * rn - cn BETWEEN 0 AND 2 THEN Tests END) AS FinancialMedian,

MAX(CASE WHEN 2 * rn - cn BETWEEN 0 AND 2 THEN Tests END) AS StatisticalMedian

FROM CTE

GROUP BY Doctor_Id

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

Nuts SSCrazy Points: 2547 More actions · Answer 4

Hi Guys

SOrry I did not send you the actual script in my post:

Here it is

USE [DATA]

GO

/****** Object: Table [dbo].[MainTable] Script Date: 06/15/2009 14:07:06 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[MainTable](

[District_Name] [varchar](200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[District_ID] [int] NULL,

[surgery_id] [int] NOT NULL,

[GP_Name] [varchar](153) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[GP_ID] [varchar](8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

[procedure_Id] [int] NOT NULL,

[Tests] [int] NULL,

[GP_Firstname] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[GP_Surname] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL

) ON [PRIMARY]

GO

SET ANSI_PADDING OFF

The region data consists of all the possible districts in the database.

Now I want to calculate the median(tests) for the different districts and one median for the whole region.

Hope this helps

Bruce W Cassidy SSChampion Points: 12668 More actions · Answer 5

[font="Verdana"]What have you tried? Mark posted some code above, and there's several pieces of code in the article I linked to you.

Basically, the median is the "centre number" in an ordered list (or usually the average of the two centre numbers if there's an even amount of numbers in the list). It's not too hard to calculate using row_number(), as Mark's code shows.

So give it a go and get back to us if you're having problems with it. 😀

[/font]

Nuts SSCrazy Points: 2547 More actions · Answer 6

The code works fine in the Management studio.

But when I use the same code in the reporting services, it gives the error for the 'CTE'.

The error says 'CTE is not a recognised Group by function.

Is there any alternative of using CTE?

Thanks

Bruce W Cassidy SSChampion Points: 12668 More actions · Answer 7

[font="Verdana"]CTE stands for Common Table Expression. You can call it anything you like, you don't have to call it CTE.

Post your code and we'll have a look at it.

[/font]

RBarryYoung SSC Guru Points: 143329 More actions · Answer 8

Nuts (6/14/2009)
The code works fine in the Management studio.
But when I use the same code in the reporting services, it gives the error for the 'CTE'.
The error says 'CTE is not a recognised Group by function.
Is there any alternative of using CTE?
Thanks

Mark's query is correct, so this sounds like a problem with how you re using Reporting Services. You might want to ask in that Forum.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]