Click here to Skip to main content
15,886,724 members
Articles / Database Development / SQL Server

Custom Aggregates in SQL Server

Rate me:
Please Sign up or sign in to vote.
4.95/5 (24 votes)
26 Mar 2011CPOL8 min read 93.1K   572   41  
SQL Server not having the aggregates you need? Why not build your own.
/// <summary>
/// Calculates the product of numerical values
/// </summary>
[System.Serializable]
[Microsoft.SqlServer.Server.SqlUserDefinedAggregate(Microsoft.SqlServer.Server.Format.Native,
    IsInvariantToDuplicates = false, // receiving the same value again changes the result
    IsInvariantToNulls = false,      // receiving a NULL value changes the result
    IsInvariantToOrder = true,       // the order of the values doesn't affect the result
    IsNullIfEmpty = true,            // if no values are given the result is null
    Name = "Product"                 // name of the aggregate
    )]
public struct Product {

    /// <summary>
    /// Used to store the product
    /// </summary>
    public System.Data.SqlTypes.SqlDouble Result { get; private set; }

    /// <summary>
    /// Used to inform if the accumulation has received values
    /// </summary>
    public bool HasValue { get; private set; }

    /// <summary>
    /// Initializes a new Product for a group
    /// </summary>
    public void Init() {
        this.Result = System.Data.SqlTypes.SqlDouble.Null;
        this.HasValue = false;
    }

    /// <summary>
    /// Calculates the product of the previous values and the value received
    /// </summary>
    /// <param name="number">Value to include</param>
    public void Accumulate(System.Data.SqlTypes.SqlDouble number) {
        if (!this.HasValue) {
            // if this is the first value received
            this.Result = number;
        }
        else if (this.Result.IsNull) {
            //if the calculated value is null, stay that way
        }
        else if (number.IsNull) {
            //if the value received is null the result is null
            this.Result = System.Data.SqlTypes.SqlDouble.Null;
        }
        else {
            //multiply the values
            this.Result = System.Data.SqlTypes.SqlDouble.Multiply(this.Result, number);
        }
        this.HasValue = true;
    }

    /// <summary>
    /// Merges this group to another group instatiated for the calculation
    /// </summary>
    /// <param name="group"></param>
    public void Merge(Product group) {
        // Count the product only if the other group has values
        if (group.HasValue) {
            this.Result = System.Data.SqlTypes.SqlDouble.Multiply(this.Result, group.Result);
        }
    }

    /// <summary>
    /// Ends the calculation and returns the result
    /// </summary>
    /// <returns></returns>
    public System.Data.SqlTypes.SqlDouble Terminate() {
        return this.Result;
    }
}

By viewing downloads associated with this article you agree to the Terms of Service and the article's licence.

If a file you wish to view isn't highlighted, and is a text file (not binary), please let us know and we'll add colourisation support for it.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Architect
Europe Europe
Biography provided

Comments and Discussions