# Median for SQL 2000

Just wondering if there was already a Median function in SQL 2000 that will perform the median by finding the middle number in a column with both even and odd numbers. I've already written a seperate select statement, but was wondering if there were a simpler way. I would also need the Median function to make sure the values in the column were numeric as some of them are 'NA' or 'N/A'-

I've been checking both EE and Googling for answers and their doesn't seem to  be anything consistent- I could be wrong

Thanks
###### Who is Participating?

x

Commented:
PAQed with points refunded (150)

Computer101
0

Commented:
No there is no median function in SQL Server... (I just answered a question to show the code for computing a median on EE about 2 weeks ago)
0

Author Commented:
aaron could you provide a link thnx
0

Author Commented:
I've found a solution thnx
0

Author Commented:
AaronAbend, if you will provide me the link to where this question was answered then I'll award you the points for responding to this question
0

Author Commented:
Page monitor, please advise me on how to handle the points for this question- thnx
0

Author Commented:
As the page editor suggested, I'm posting the median solution that  I am using:

Here's the median statement I'm using, it also includes Quartiles and an average, but nonetheless..

SELECT '1. Promptness' AS Quality,
(
SELECT AVG(
CASE
WHEN prac_promptness NOT IN ('N/A', 'NA')
THEN Convert(integer,prac_promptness)
ELSE NULL
END
)
FROM dbo.TBL_EDTL_PRACEVAL
WHERE prac_term = 'Fall Semester 2006'
) [Average Score],

(
SELECT SUM(promptness) / COUNT(promptness)
FROM (
SELECT TOP 25 PERCENT
Convert(integer, prac_promptness )as promptness
FROM dbo.TBL_EDTL_PRACEVAL
WHERE prac_term = 'Fall Semester 2006'
AND prac_promptness not in ( 'N/A','NA')
ORDER BY Convert(integer, prac_promptness)
) AS X
) AS Quartile1,

(
SELECT SUM(promptness)/COUNT(promptness)
FROM (
SELECT TOP 25 PERCENT promptness
FROM (
SELECT TOP 50 PERCENT Convert(integer, prac_promptness )as promptness
FROM dbo.TBL_EDTL_PRACEVAL
WHERE prac_term = 'Fall Semester 2006'
AND prac_promptness not in ( 'N/A','NA')
ORDER by Convert(integer, prac_promptness) DESC
) Src
ORDER BY promptness
) tt
) AS Quartile3,

(SELECT
(SELECT TOP 1 Convert(integer, prac_promptness)
FROM (
SELECT TOP 50 PERCENT prac_promptness
FROM dbo.TBL_EDTL_PRACEVAL
WHERE prac_promptness NOT IN ( 'N/A','NA')
ORDER BY Convert(integer, prac_promptness)
) s1
) +
(SELECT TOP 1 Convert(integer, prac_promptness)
FROM (
SELECT TOP 50 PERCENT prac_promptness
FROM dbo.TBL_EDTL_PRACEVAL
WHERE prac_promptness NOT IN ( 'N/A','NA')
ORDER BY Convert(integer, prac_promptness) DESC
) s2
)
/ 2.0) AS Median

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.