Mean vs Median that is the?

Question

I have a number of select statements which calculate fields such as sums, division and also averages. However I am now needing to include a median based on the query, as well as a mean(avg). The table contains 50,000 rows in the MSSQL database, so in the same query return I need to have the results return for each line.

I know there is not a Median formula in SQl, well not that I am aware of. I am using SQL 2012. So if anyone has an idea, I would welcome your thoughts, as I can not be the only person to come up against this.

Example would be something like this

Select 
     Round (AVG ([LENGTH]),2) as Length_X,
     Round (Median ([LENGTH]),2) as Length_median,

I understand Median is not an accepted sql statement, so just for demo purposes to get my point across

Cheers

"I can not be the only person to come up against this." And yet you still decided to ask this question. :) — aquinas, May 04 '17 at 14:57
Median is the same as 50th Percentile. Percentile exists as a function from 2012 onwards — Jamiec, May 04 '17 at 14:58
[Some ways are better than others](https://sqlperformance.com/2012/08/t-sql-queries/median). — Aaron Bertrand, May 04 '17 at 14:59
Not very helpful Aquinas, but hey, life always needs a laugh — Processit, May 04 '17 at 19:32

score 1 · Accepted Answer · answered May 04 '17 at 14:57

If you have numeric data, then there is a "median" function. It is just spelled differently:

select percentile_cont(0.5) within group (order by ??) over ()

or

select percentile_disc(0.5) within group (order by ??) over ()

(The difference between the two is subtle and probably doesn't matter for most purposes.)

It is, unfortunately, not an aggregation function, so it is often used in a subquery.

Mean vs Median that is the?

1 Answers1