Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

# Median for SQL 2000

Posted on 2006-10-30
Medium Priority
920 Views
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
0
Question by:friskee
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 5

LVL 10

Expert Comment

ID: 17836970
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 Comment

ID: 17842660
aaron could you provide a link thnx
0

Author Comment

ID: 17867121
I've found a solution thnx
0

Author Comment

ID: 17905859
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 Comment

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

Author Comment

ID: 18189761
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

LVL 1

Accepted Solution

Computer101 earned 0 total points
ID: 18200041
PAQed with points refunded (150)

Computer101
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have a large data set and a SSIS package. How can I load this file in multi threading?
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this â€¦
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
###### Suggested Courses
Course of the Month5 days, 12 hours left to enroll