Solved

Median for SQL 2000

Posted on 2006-10-30
9
912 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

911 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

25 Experts available now in Live!

Get 1:1 Help Now