How to calculate Median?

[Per-Olof Nilsson]

Hello

I exptected to find an aggregated function, like AVG, for calculating
median. But I didn't find such a function.

Anyone have a good solution of how to calculate median?

/Per-Olof

[Anith Sen]

You can use the following general methodolgy using TOP to
get the median value. Assuming you have a unique Sequence
or Ranking column, you can try:

SELECT AVG(SeqCol)
FROM (
SELECT MAX(SeqCol)
FROM (
SELECT TOP 50 PERCENT SeqCol
FROM yourTable
ORDER BY SeqCol ASC
) AS _D1(SeqCol)
UNION ALL
SELECT MIN(SeqCol)
FROM (
SELECT TOP 50 PERCENT SeqCol
FROM yourTable
ORDER BY SeqCol DESC
) AS _D2(SeqCol)
) AS _Derived(SeqCol)

--
- Anith

[Dejan Sarka]

Check the Code Library - White Papers at http://sql.reproms.si. I've posted
there many statistical functions with explanation.

--
Dejan Sarka, SQL Server MVP
FAQ from Neil & others at: http://www.sqlserverfaq.com
Please reply only to the newsgroups.
PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org

"Per-Olof Nilsson" <per-olof...@inxl.se> wrote in message
news:OuuCKnPdCHA.1688@tkmsftngp09...