473,886 Members | 2,540 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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_N AME) type function or a
more general function like a 10% percentile function with syntax such

Regards JC......
Jul 23 '05 #1
2 7050
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 records with a given foreign-key value, essentially: Create table table1 { fk_id number, seq_id number
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 1/1/2004 4000.00 40.00 4040.00 1/2/2004 1000.00 10.00 1010.00 1/2/2204 2000.00 20.00 2020.00 1/3/2004 1500.00 15.00 1515.00 I want my resultant DataTable to show in DataGrid as
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 post) *I'm running a total query, of the form
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 in MySQL 4.x) Particularly, I find GROUP_CONCAT practical to quickly display 1-many
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 in one-to-many relation. I don't need to see all values in B that relate to the particular record in A, just one value in each field in B, preferably the last entered. This is to ease a person that need to manually fix and encode
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 custom aggregate functions in DB2, written close to three years ago by Knut Stolze: http://www-128.ibm.com/developerworks/db2/library/techarticle/0309stolze/0309stolze.html Mr. Stolze says in the first article that "urrently, there is no
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 processing time. I have not tried this with different recordsets yet. I am curious what others might think. What might be interesting if you could create temporary indexes. I did try sorting the recordset on find first. It ran slower. I have...
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.: SELECT A, sum(B) as Answer1, Answer1 * 2 as DoubleAnswer
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: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

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.