Explain User-defined Aggregates ?

A user-defined aggregate (UDA ) function returns a scalar result that is the result of a calculation on values in a set of rows. Examples of such functions include built-in SQL Server aggregate functions such as SUM, AVG, MIN, and MAX. A CLR UDA function is implemented as a structure or class in a .NET Framework assembly. A CLR UDA function can be invoked in T-SQL statements with the same rules that apply to system aggregate functions.

The following example creates, registers, and executes a UDA function that returns the sum of a SqlMoney column in a table

 using System;

    using System.Data;

    using System.Data.Sql;

    using System.Data.SqlTypes;

    using Microsoft.SqlServer.Server;

    [Serializable]

    [SqlUserDefinedAggregate(Format.Native)]

    public struct SumMoney

    {

        private SqlMoney sum;

        public void Init(  )

        {

            sum = 0;

        }

        public void Accumulate(SqlMoney Value)

        {

            sum += Value;

        }

        public void Merge(SumMoney Group)

        {

            sum += Group.sum;

        }

        public SqlMoney Terminate(  )

        {

            return sum;

        }

    }

In SQL Server Management Studio, register the assembly and create the UDA function by executing the following statement:

    USE AdventureWorks

    GO

    CREATE ASSEMBLY SumMoney

    FROM 'C:\PSS2005\Uda\Uda\bin\Debug\Uda.dll'

    GO

    CREATE AGGREGATE SumMoneyUda

    ( @Value money )

    RETURNS money

    EXTERNAL NAME SumMoney.SumMoney

Execute the aggregate function on the Sales.SalesOrderHeader table in Aventure-Works:

    SELECT dbo.SumMoneyUda(SubTotal), dbo.SumMoneyUda(TaxAmt),

      dbo.SumMoneyUda(Freight), dbo.SumMoneyUda(TotalDue)

    FROM Sales.SalesOrderHeader