Custom aggregate functions

yes. look for UDF (user defined function) in books online.

have a look at http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsqlpro01/html/sql01l1.asp

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.

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

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.

Create a cursor and do what you want! What are your trying to do?

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

do you know how to do "logical OR-ed or AND-ed" in SQL?

OR: 4|6|20 = 22
AND: 4&6&20 = 4

SUM: 4+6+20 = 30

i was able to do the OR-ed

DECLARE @A int
SET @A = 0

SELECT @A = @A | Field1
FROM  Table2

select @A

and here comes the AND-ed:

DECLARE @A int
SET @A = 9999999

SELECT @A = @A & Field1
FROM  Table2

select @A

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

Sorry to say, but these function are still table-specific...

pass a tablename and a fieldname to the functions, create a query into your function and execute it from the function.

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.

I have not tried grouping with UDF.

Ok, thanks.

Consider this question closed   :)