Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 924
  • Last Modified:

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
0
friskee
Asked:
friskee
  • 5
1 Solution
 
AaronAbendCommented:
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
 
friskeeAuthor Commented:
aaron could you provide a link thnx
0
 
friskeeAuthor Commented:
I've found a solution thnx
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
friskeeAuthor 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
 
friskeeAuthor Commented:
Page monitor, please advise me on how to handle the points for this question- thnx
0
 
friskeeAuthor 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
 
Computer101Commented:
PAQed with points refunded (150)

Computer101
EE Admin
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now