How to calculate product aggregate function in SQL Server

Question

I have a table with 2 columns:

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

I want to add a column whose contents are multiplied by a sequential column as follows:

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

How can i do this?

Answer 1

Oh it's a pain. Most databases do not support the product aggregation function. You can emulate it using logs and permissions. 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() can be called ln() in some databases. Also, this works for positive numbers. Variations exist for handling negative numbers and zeros, but this complicates the answer (and the sample data is all positive).

Answer 2

Creating a CLR aggregate is not so bad. I freaked out after 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; } } 

After that, you can use the methods commonly used for the current amount (i.e. a triangular join or a window definition that restricts rows, depending on your sql version).