Jump to content
  • 0

Median Calculation


Elena

Question

Hello all,

 

First off, thank you for all the assistance given to me by the folks here at the community.

 

I am hoping to pick your brains yet again:

 

Is there an easier way to calculate the Median with SQL 2008 (which is the version that I am in, based on the plan that I purchased with Caspio)?

 

Below is my code, which is not working:

WITH Cnt AS
(SELECT COUNT(*) AS cnt)
,RN AS
(SELECT AutoID, ROW_NUMBER() OVER (ORDER BY Annual_Salaries) AS myNum
FROM market_data
)
SELECT AVG(1.*Annual_Salaries) AS median
FROM RN JOIN Cnt
on myNum IN ((cnt+1)/2,(cnt+2)/2)

Any help will be put in the bank of 'never to be forgotten'.

 

Elena

 

 

 

 

 

 

Link to comment
Share on other sites

2 answers to this question

Recommended Posts

  • 0

Hi everyone!

I had the same question on how to calculate median and found out that there is no built-in function in SQL for that:(

I have found a workaround though, here is my example:

SELECT
(
 (SELECT MAX(Price) FROM
   (SELECT TOP 50 PERCENT Price FROM Products_sold ORDER BY Price ) AS BottomHalf)
 +
 (SELECT MIN(Price) FROM
   (SELECT TOP 50 PERCENT Price FROM Products_sold ORDER BY Price DESC) AS TopHalf)
) / 2 AS Median

Where 'Price' is the field name, 'Products_sold' - table name. You want to make sure to change these to your field/table names.

Here is a helpful external resource as well: https://stackoverflow.com/questions/1342898/function-to-calculate-median-in-sql-server

I hope that it will be helpful for someone!

Link to comment
Share on other sites

  • 0
On ‎3‎/‎25‎/‎2016 at 7:08 PM, Elena said:

Is there an easier way to calculate the Median with SQL 2008 (which is the version that I am in, based on the plan that I purchased with Caspio)?

 

Hi Elena,

To the best of my knowledge MSSQL does not have a native Median() function that I am aware of.   You seem to basically be doing what you need to.  Sorting based on the field in question and then going to the middle row and taking that value.  If you are still having a problem with this I can mess around with it.

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Answer this question...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...
×
×
  • Create New...