Solved

Median for SQL 2000

Posted on 2006-10-30
9
911 Views
Last Modified: 2012-06-21
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
Comment
Question by:friskee
  • 5
9 Comments
 
LVL 10

Expert Comment

by:AaronAbend
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

by:friskee
ID: 17842660
aaron could you provide a link thnx
0
 

Author Comment

by:friskee
ID: 17867121
I've found a solution thnx
0
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 

Author Comment

by:friskee
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

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

Author Comment

by:friskee
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

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

Computer101
EE Admin
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Suggested Solutions

In this article—a derivative of my DaytaBase.org blog post (http://daytabase.org/2011/06/18/what-week-is-it/)—I will explore a few different perspectives on which week today's date falls within using Microsoft SQL Server. First, to frame this stu…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
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.

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now