Search code examples
sqlsql-serversumaggregate-functionsexp

How to Calculate Aggregated Product Function in SQL Server

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?

Solution

  • 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).