Writing your own aggregate functions

[jc]

Hi.
Just as we have AVG(COLUMN_NAME) and MAX(COLUMN_NAME) how can I write
my own variation of a such a function. If I can appreciate how to do
this,
then I hopefully I can write a MEDIAN(COLUMN_NAME) type function or a
more general function like a 10% percentile function with syntax such
as
PERCENTILE(COLUMN_NAME,25).

Regards JC......

[David Portas]

In SQL Server 2005 you can define your own aggregate functions with CLR
code. Meantime many types of aggregates are possible in TSQL queries.
Here are two examples. You can Google for others.

http://www.aspfaq.com/show.asp?id=2506
http://www.google.co.uk/groups?selm=...40giganews.com

--
David Portas
SQL Server MVP
--

[--CELKO--]

Aggregate functions can be "faked" with expressions as the parameters
of the existing functions you get.

hopefully I can write a MEDIAN (<exp>) type function or a more

general function like a 10% percentile function with syntax such as
PERCENTILE(<exp>, <int>). <<

The Median is actually harder than you would think. Get a copy of SQL
FOR SMARTIES where I have a whole chapter on half a dozen ways to do
it.

Google for the code for a product aggregate, and consider all the
things youcan do with a CASE expression.

The bad news is that SQL Server 2005 will you define your own aggregate
functions in a CLR language. That means that you will get different
rounding and truncation, different defintions of MOD() and other
functions all in the same database.

Of course all average VB, C#, COBOL, etc. programmers has a mathematics
course in floating error corrections that they will use when they write
these aggregates.

Safest thing to do; get a stat package for doing statistics beyond
simpe descriptive values. Those guys do have the PhD in the shop to
get it right.