Suppose i have table and one column has numeric value. now how could i calculate median value ?
i found one code from this url https://stackoverflow.com/questions/1342898/function-to-calculate-median-in-sql-server
but their approach is not clear
DECLARE @table AS TABLE
(
Number int not null
);
insert into @table select 2;
insert into @table select 4;
insert into @table select 9;
insert into @table select 15;
insert into @table select 22;
insert into @table select 26;
insert into @table select 37;
insert into @table select 49;
DECLARE @Count AS INT
SELECT @Count = COUNT(*) FROM @table;
WITH MyResults(RowNo, Number) AS
(
SELECT RowNo, Number FROM
(SELECT ROW_NUMBER() OVER (ORDER BY Number) AS RowNo, Number FROM @table) AS Foo
)
SELECT AVG(Number) FROM MyResults WHERE RowNo = (@Count+1)/2 OR RowNo = ((@Count+1)%2) * ((@Count+2)/2)
1) RowNo = (@Count+1)/2 not clear
2) RowNo = ((@Count+1)%2) * ((@Count+2)/2) not clear
what they are doing?
any easy approach exist for median calculation? thanks

