I need to find the median of a column and the answer needs to be rounded to 4 decimal places. Since sql server doesn't have the "MEDIAN()" function, I needed to get the smallest number from the top 50% of the list and the biggest of the bottom 50% and then divide by 2.
I tried to do it like this:
SELECT(
(SELECT CAST(ROUND(MAX(LAT_N), 4) AS DECIMAL(8, 4))
FROM (SELECT TOP 50 PERCENT LAT_N FROM STATION ORDER BY LAT_N ASC) AS Bottom1)
+ (SELECT CAST(ROUND(MIN(LAT_N), 4) AS DECIMAL(8, 4))
FROM (SELECT TOP 50 PERCENT LAT_N FROM STATION ORDER BY LAT_N DESC) AS Top1)) / 2;
But the result is 5.323200 instead of 5.3232.
I also tried this:
SELECT(
(SELECT CAST(ROUND(MAX(LAT_N), 4) AS DECIMAL(8, 4))
FROM (SELECT TOP 50 PERCENT (LAT_N / 2) AS LAT_N FROM STATION ORDER BY LAT_N ASC) AS Bottom1)
+ (SELECT CAST(ROUND(MIN(LAT_N), 4) AS DECIMAL(8, 4))
FROM (SELECT TOP 50 PERCENT (LAT_N / 2) AS LAT_N FROM STATION ORDER BY LAT_N DESC) AS Top1));
This answer seems to be close, but by dividing both the min and the max, I end up losing 0.0001 which makes the answer wrong.
How can I fix either of the ways I'm trying to do it in order to get a median from a table. Or is there another way to do it?
5.323200
and5.3232
are the same value, so what is your point? If you only want the value accurate to 4 decimal places thenCAST
/CONVERT
to adecimal
with the appropriate scale.MEDIAN
, but it does havePERCENTILE_CONT
. The median isPERCENTILE_CONT(0.5)
.