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?
CodePudding user response:
I have been reading the T-SQL Querying by Itzik Ben-Gan and recently came across the BI section. I believe this is what you want. This is how he explains to get the median. He calls this the financial median. Your other option is to look at the PERCENTILE_DISC instead of the PERCENTILE_CONT as shown below.
CREATE TABLE #Test (
[Id] INT IDENTITY(1,1) CONSTRAINT PK_Id PRIMARY KEY,
[Cost] DECIMAL(19,4) NOT NULL
);
INSERT INTO #Test([Cost])
VALUES (5),(10),(15);
SELECT DISTINCT
PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY [Cost]) OVER () AS median
FROM #Test