Timeline for Function to Calculate Median in SQL Server
Current License: CC BY-SA 2.5
12 events
| when toggle format | what | by | license | comment | |
|---|---|---|---|---|---|
| Nov 20, 2019 at 18:44 | review | Suggested edits | |||
| Nov 20, 2019 at 22:12 | |||||
| Jul 4, 2019 at 14:47 | comment | added | Hugues Paquet Blanchette | If "Score" field is nullable, the median won't compute. You can specify in query: 'SELECT ( (SELECT MAX(Score) FROM (SELECT TOP 50 PERCENT Score FROM Posts Where Score is not null ORDER BY Score) AS BottomHalf) + (SELECT MIN(Score) FROM (SELECT TOP 50 PERCENT Score FROM Posts Where Score is not null ORDER BY Score DESC) AS TopHalf) ) / 2 AS Median' | |
| Jan 25, 2019 at 18:23 | review | Suggested edits | |||
| Jan 25, 2019 at 19:10 | |||||
| Oct 29, 2018 at 22:16 | comment | added | brianary |
You can also UNION the MAX and MIN in a CTE, and take the AVG of them.
|
|
| Feb 6, 2018 at 1:46 | comment | added | Konstantin Chernov | Is this correct for odd number of results? Like for 9 elements median is the fifth one. | |
| May 13, 2015 at 9:32 | comment | added | Przemyslaw Remin | How to use this solution with a GROUP BY? | |
| Dec 13, 2013 at 11:37 | comment | added | Stu Harper | Tomas - did you manage to solve your "per certain group category" issue plese? As I have the same problem. Thanks. | |
| Jul 1, 2013 at 13:02 | comment | added | Tomas | ... I mean like in this case (the 2nd query named "Users with highest median answer score"). | |
| Jul 1, 2013 at 12:01 | comment | added | Tomas |
Well, nice and simple, but usualy you need median per certain group category, i.e. like select gid, median(score) from T group by gid. Do you need a correlated subquery for that?
|
|
| Mar 26, 2013 at 23:03 | review | Suggested edits | |||
| Mar 26, 2013 at 23:04 | |||||
| Jan 31, 2012 at 19:42 | comment | added | Katie Kilian | That's clever, and relatively simple given that there exists no Median() aggregate function. But how is it that no Median() function exists!? I'm a bit FLOOR()ed, frankly. | |
| Jan 8, 2010 at 9:34 | history | answered | Jeff Atwood | CC BY-SA 2.5 |