Median Calculation

[Elena]

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

 

 

 

 

 

 

[sandy159]

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!

[RussellParker]

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.