469,950 Members | 1,456 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,950 developers. It's quick & easy.

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......
Jul 23 '05 #1
2 6852
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
--

Jul 23 '05 #2
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.

Jul 23 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

3 posts views Thread by Ariel Jakobovits | last post: by
5 posts views Thread by David Garamond | last post: by
8 posts views Thread by jefftyzzer | last post: by
118 posts views Thread by Chuck Cheeze | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.