There is a good article at ASPFAQ on how to calculate median in a SQL
table. (http://databases.aspfaq.com/database...calculate-the-
median-in-a-table.html). I would like to either turn that into a
stored procedure, or ideally turn it into a custom aggregate function
do I could use it with a GROUP BY clause.

There are details and caveats in the article, but the gist of the SQL
is:
SELECT AVG(splunge) FROM
(
SELECT splunge FROM (
SELECT TOP 1 splunge = splunge * 1.0 FROM
(
SELECT TOP 50 PERCENT splunge
FROM blat ORDER BY splunge
) sub_a
ORDER BY 1 DESC
) sub_1
UNION ALL
SELECT splunge FROM (
SELECT TOP 1 splunge = splunge * 1.0 FROM
(
SELECT TOP 50 PERCENT splunge
FROM blat ORDER BY splunge DESC
) sub_b
ORDER BY 1
) sub_2
) median

Is it possible to create custom aggregate functions in SQL? If not, is
it possible to pass an array into an SP so the SP could return the
median? I haven't found any direct way of doing either of these.

Thanks in advance for any tips, links, or anything that points me in
the right direction.

You can create custom aggregate with CLR:
http://msdn.microsoft.com/en-us/library/ms131057.aspx

But if you are using SQL Server 2008 you can pass table-valued parameters to functions and stored procedures and
calculate median that way.
http://msdn.microsoft.com/en-us/library/bb510489.aspx

--
Plamen Ratchev
http://www.SQLStudio.com

On Apr 9, 9:43*am, Plamen Ratchev <Pla... (AT) SQLStudio (DOT) com> wrote:
Thanks Plamen,

After posting, I found a note where you helped me on a similar issue a
few years ago. Unfortunately after reading through those articles, it
looks like this is harder than I hoped, and probably outside the scope
of what I should do for this project. I'll go back to what you helped
me with earlier, and work it out without a custom aggregate.

HumanJHawkins (JHawkins (AT) Locutius (DOT) Com) writes:
You can use the row_number function to compute the median:

; WITH numbered AS (
SELECT a, b, c, d, val,
rowno = row_number() OVER (PARTITION BY a, b, c, d
ORDER BY val),
cnt = COUNT(*) OVER(PARITION BY a, b, c, d)
FROM tbl
)
SELECT a, b, c, d, AVG(val)
FROM numbered
WHERE rowno IN (cnt/2 + 1, (cnt + 1)/2)
GROUP BY a, b, c, d

The CTE numbers the rows and counts them per your grouping columns.
The CTE is then filtered so that only the middle row(s) is retained,
and if there are two middle rows, we take the average of that. It's
important that your GROUP BY clause matches your PARTITION BY clause.


As for "arrays", see http://www.sommarskog.se/arrays-in-sql.html on my
web site for a discussion.


--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx