Finding a median in sql server

October 28, 2022

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?

>Solution :

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