472,368 Members | 2,604 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,368 software developers and data experts.

Writing your own aggregate functions

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
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

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


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

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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

by: Ariel Jakobovits | last post by:
I have a table with 2 primary keys, one is a foreign key, the other is produced by a sequence. I want to SELECT query for one record that has a list of the sequence-produced values for all...
by: Job Lot | last post by:
Is it possible to use Aggregate functions with GROUP BY Clauses on DataTable. I have a DataTable with following values: Date Amount Int Balance 1/1/2004 5000.00 50.00 5050.00...
by: neb | last post by:
Dear member of the forum, Ms access has built-in aggregate function like: -Sum, Max, First, Avg, ... Is it possible to build user-defined aggregate? (if you have any clue, do not hesitate to...
by: David Garamond | last post by:
What do people think of adding some more aggregate functions. These are the ones that MySQL has and PG doesn't: - STD/STDDEV - VARIANCE - BIT_OR - BIT_AND - GROUP_CONCAT (for strings, added...
by: S P Arif Sahari Wibowo | last post by:
Hi! I would like to make an editable continous form, where most fields will be from table A and editable, except 1-3 fields are a glimpse into table B and uneditable. Table A relate to table B...
by: jefftyzzer | last post by:
The current issue of "Oracle Magazine" has an article on creating custom aggregate functions, which naturally got me thinking about how to do this in DB2. I found some articles on creating...
by: Dean | last post by:
Has anyone toiled with creating/using alternate domain aggregate functions? I have been messing with that a little. The one recordsource I have been working indicates I get 20 to 40% savings in...
by: BillCo | last post by:
just wasted a long time figuring out this and I figure if I post it might save someone some pain! Jet (DAO) will allow you to to use nested aggregate functions like building blocks, e.g.: ...
by: Chuck Cheeze | last post by:
This might be in the wrong group, but... Here is an example of my data: entry_id cat_id 1 20 2 25 3 30 4 25 5 35
by: Naresh1 | last post by:
What is WebLogic Admin Training? WebLogic Admin Training is a specialized program designed to equip individuals with the skills and knowledge required to effectively administer and manage Oracle...
by: Arjunsri | last post by:
I have a Redshift database that I need to use as an import data source. I have configured the DSN connection using the server, port, database, and credentials and received a successful connection...
by: WisdomUfot | last post by:
It's an interesting question you've got about how Gmail hides the HTTP referrer when a link in an email is clicked. While I don't have the specific technical details, Gmail likely implements measures...
by: Matthew3360 | last post by:
Hi, I have been trying to connect to a local host using php curl. But I am finding it hard to do this. I am doing the curl get request from my web server and have made sure to enable curl. I get a...
by: Oralloy | last post by:
Hello Folks, I am trying to hook up a CPU which I designed using SystemC to I/O pins on an FPGA. My problem (spelled failure) is with the synthesis of my design into a bitstream, not the C++...
by: BLUEPANDA | last post by:
At BluePanda Dev, we're passionate about building high-quality software and sharing our knowledge with the community. That's why we've created a SaaS starter kit that's not only easy to use but also...
by: Rahul1995seven | last post by:
Introduction: In the realm of programming languages, Python has emerged as a powerhouse. With its simplicity, versatility, and robustness, Python has gained popularity among beginners and experts...
by: Johno34 | last post by:
I have this click event on my form. It speaks to a Datasheet Subform Private Sub Command260_Click() Dim r As DAO.Recordset Set r = Form_frmABCD.Form.RecordsetClone r.MoveFirst Do If...
by: ezappsrUS | last post by:
Hi, I wonder if someone knows where I am going wrong below. I have a continuous form and two labels where only one would be visible depending on the checkbox being checked or not. Below is the...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.