User-Defined Aggregates

An aggregate is a function that returns a single value based on multiple rows. Examples of built-in aggregates are COUNT, AVG, and SUM. COUNT returns the record count of all selected records, AVG returns the average of values from a column of selected rows, and SUM returns the sum of all values of a column. All built-in aggregates work only with built-in value types.

A simple usage of the built-in aggregate AVG is shown here to return the average unit price of all products from the Northwind database by passing the UnitPrice column to the AVG aggregate in the SELECT statement:

 SELECT AVG(UnitPrice) AS 'average unit price'  FROM dbo.Products 

The SELECT statement just returns a single value that represents the average of all UnitPrice column values. Aggregates can also work with groups. In the next example the AVG aggregate is combined with the GROUP BY clause to return the average unit price of every supplier:

 SELECT SupplierID, AVG(UnitPrice) AS 'average unit price'  FROM dbo.Products GROUP BY SupplierID 

For custom value types, and if you want to do a specific calculation based on a selection of rows, you can create a user-defined aggregate.

Creating User-Defined Aggregates

To write a user-defined aggregate with CLR code, a simple class with the methods Init(), Accumulate(), Merge(), and Terminate() must be implemented. The functionality of these methods is shown in the following table.

UDT Method	Description
Init()	The Init() method is invoked for every group of rows to be processed. In this method, initialization can be done for calculation of every row group.
Accumulate()	The method Accumulate() is invoked for every value in all groups. The parameter of this method must be of the correct type that is accumulated; this can also be the class of a user-defined type.
Merge()	The method Merge() is invoked when the result of one aggregation must be combined with another aggregation.
Terminate()	After the last row of every group is processed, the method Terminate() is invoked. Here the result of the aggregate must be returned with the correct data type.

The code sample shows how to implement a simple user-defined aggregate to calculate the sum of all rows in every group. For deployment with Visual Studio, the attribute [SqlUserDefinedAggregate] is applied to the class SampleSum. As with the user-defined type, with user-defined aggregates the format for storing the aggregate must be defined with a value from the Format enumeration. Again, Format.Native is for using the automatic serialization with blittable data types.

The variable sum is used for accumulation of all values of a group. In the Init() method, the variable sum is initialized for every new group to accumulate. The method Accumulate() that is invoked for every value adds the value of the parameter to the sum variable. With the Merge() method, one aggregated group is added to the current group. Finally, the method Terminate() returns the result of a group:

 [Serializable] [SqlUserDefinedAggregate(Format.Native)] public struct SampleSum { private int sum; public void Init() { sum = 0; } public void Accumulate(SqlInt32 Value) { sum += Value.Value; } public void Merge(SampleSum Group) { sum += Group.sum; } public SqlInt32 Terminate() { return new SqlInt32(sum); } } 

Using User-Defined Aggregates

The user-defined aggregate can be deployed either with Visual Studio 2005 or with the CREATE AGGREGATE statement:

 CREATE AGGREGATE [SampleSum] (@value int) RETURNS [int] EXTERNAL NAME  [Demo].[SampleSum] 

After the user-defined aggregate has been installed, it can be used as shown in the following SELECT statement, where the number of ordered products is returned by joining the Products and Order Details tables. For the user-defined aggregate, the Quantity column of the Order Details table is defined as an argument:

SELECT DISTINCT dbo.[Order Details].ProductID AS 'Id', dbo.SampleSum(dbo.[Order Details].Quantity) AS 'Sum' FROM dbo.Products INNER JOIN dbo.[Order Details] ON dbo.Products.ProductID = dbo.[Order Details].ProductID GROUP BY dbo.[Order Details].ProductID, dbo.Products.ProductName ORDER BY dbo.[Order Details].ProductID