Custom aggregate functions

Is it possible to create my own aggregate functions like MAX, COUNT, SUM, etc. in MS-SQL 2000?

How?
LVL 13
EpsylonAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Éric MoreauConnect With a Mentor Senior .Net ConsultantCommented:
I have seen a SQL expert tonight who was talking about UDF and he confirmed me that there is no way to create your own function like SUM because UDF needs to be deterministic (everything must be know at compile time : table, where clause, ...)
0
 
Éric MoreauSenior .Net ConsultantCommented:
yes. look for UDF (user defined function) in books online.
0
 
Éric MoreauSenior .Net ConsultantCommented:
0
Build your data science skills into a career

Are you ready to take your data science career to the next step, or break into data science? With Springboard’s Data Science Career Track, you’ll master data science topics, have personalized career guidance, weekly calls with a data science expert, and a job guarantee.

 
EpsylonAuthor Commented:
Thanks for your response.

Only I can't find here how to create an aggregate function like SUM which does not just use a single field in a single row, but uses *all* rows.
0
 
Éric MoreauSenior .Net ConsultantCommented:
This is an example of retreiving an aggregate from a function.

Here is a function:

CREATE FUNCTION dbo.AvgPrice( @StockId int )
RETURNS real
AS
BEGIN
  DECLARE @avg real
  SELECT @avg = avg(Price) FROM StockPrice
    WHERE StockID = @StockId
  RETURN ( @avg )
END
GO

Here is the call:

SELECT dbo.AvgPrice(2), *
FROM [StockPrice]
where StockId = 2

0
 
EpsylonAuthor Commented:
Thanks for trying to help, but this is still not what I am looking for.

The function dbo.AvgPrice() works only on the table 'StockPrice'. MAX(), SUM(), COUNT(), AVG(), etc. (with proper parameters) work on every table.
0
 
Éric MoreauSenior .Net ConsultantCommented:
Create a cursor and do what you want! What are your trying to do?
0
 
EpsylonAuthor Commented:
What I want is an aggregate function that results in for example a logical OR-ed or AND-ed result:

select doOR(intfield) from sometable
select doAND(intfield) from sometable

just like

select SUM(intfield) from sometable



So when I have this table:

intfield
--------------
4
6
20


Results:

select doOR(intfield) from sometable ===> 22
select doAND(intfield) from sometable ===> 4
0
 
Éric MoreauSenior .Net ConsultantCommented:
do you know how to do "logical OR-ed or AND-ed" in SQL?
0
 
EpsylonAuthor Commented:
OR: 4|6|20 = 22
AND: 4&6&20 = 4

SUM: 4+6+20 = 30
0
 
Éric MoreauSenior .Net ConsultantCommented:
i was able to do the OR-ed

DECLARE @A int
SET @A = 0

SELECT @A = @A | Field1
FROM  Table2

select @A
0
 
Éric MoreauSenior .Net ConsultantCommented:
and here comes the AND-ed:

DECLARE @A int
SET @A = 9999999

SELECT @A = @A & Field1
FROM  Table2

select @A
0
 
Éric MoreauSenior .Net ConsultantCommented:
CREATE FUNCTION dbo.ANDED( )
RETURNS int
AS
BEGIN
DECLARE @A int
SET @A = 9999999

SELECT @A = @A & Field1
FROM  Table2
 RETURN ( @A )
END
GO

CREATE FUNCTION dbo.ORED( )
RETURNS int
AS
BEGIN
DECLARE @A int
SET @A = 0

SELECT @A = @A | Field1
FROM  Table2
 RETURN ( @A )
END
GO

--Here is the call:

SELECT dbo.ANDED(), dbo.ORED()
0
 
EpsylonAuthor Commented:
Sorry to say, but these function are still table-specific...
0
 
Éric MoreauSenior .Net ConsultantCommented:
pass a tablename and a fieldname to the functions, create a query into your function and execute it from the function.
0
 
EpsylonAuthor Commented:
Sorry, but this can only work when the result of the query has only one record. When grouping of all records results in more than one records, it will go wrong.
0
 
Éric MoreauSenior .Net ConsultantCommented:
I have not tried grouping with UDF.
0
 
EpsylonAuthor Commented:
Ok, thanks.

Consider this question closed   :)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.