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?
 
Éric MoreauSenior .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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
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.