How to Calculate Aggregated Product Function in SQ

Question

I have a table with 2 column:

No.  Name    Serial
1    Tom       1
2    Bob       5
3    Don       3
4    Jim       6

I want to add a column whose a content is multiply Serial column like this:

No.  Name    Serial   Multiply
1    Tom       2         2
2    Bob       5         10
3    Don       3         30
4    Jim       6         180

How can i do that?

Answer 1

Oh, this is a pain. Most databases do not support a product aggregation function. You can emulate it with logs and powers. So, something like this might work:

select t.*,
       (select exp(sum(log(serial)))
        from table t2
        where t2.no <= t.no
       ) as cumeProduct
from table t;

Note that log() might be called ln() in some databases. Also, this works for positive numbers. There are variations to handle negative numbers and zeroes, but this complicates the answer (and the sample data is all positive).

Answer 2

Creating the CLR aggregate isn't so bad. I whipped this up in about 5 minutes:

[Serializable]
[Microsoft.SqlServer.Server.SqlUserDefinedAggregate(Format.Native)]
public struct Product
{
    private SqlDouble _p;
    public void Init()
    {
        this._p = new SqlDouble(1);
    }

    public void Accumulate(SqlDouble Value)
    {
        this._p *= Value;
    }

    public void Merge (Product Group)
    {
        this._p *= Group._p;
    }

    public SqlDouble Terminate ()
    {
        // Put your code here
        return _p;
    }
}

Once you've got that, you can use the techniques usually used for a running sum (i.e. a triangular join or a window definition that bounds the rows, depending on your version of sql).