Example of Creating a User-Defined Aggregate

The following example defines a user-defined aggregate named average: 
CREATE AGGREGATE average
   WITH (
      INIT = average_init,
      ITER = average_iter,
      COMBINE = average_combine,
      FINAL = average_final
      );

Before you use the average aggregate in a query, you must also use CREATE FUNCTION statements to create the support functions specified in the CREATE AGGREGATE statement.

The following table gives an example of the task that each support function might perform for average.

Keyword Support Function Effect
INIT average_init Allocates and initializes an extended data type storing the current sum and the current row count
ITER average_iter For each row, adds the value of the expression to the current sum and increments the current row count by one
COMBINE average_combine Adds the current sum and the current row count of one partial result to the other and returns the updated result
FINAL average_final Returns the ratio of the current sum to the current row count and converts this ratio to the result type