Well we all know how to add the values in a column using SUM and GROUP BY.  But what about multiplying the column values?  And also how to calculate running multiplication on a column similar to Running Totals?

Sometime back I came across this interesting question on MSDN SQL Server forumsHow to multiply all values in a table column? I don’t know the reason of such requirement, but very interesting problem though.  Of course there is no such built-in function in SQL Server to do it for you.  But after thinking on it for few minutes, I realize that if you still remember the basics of Mathematics, it is not that difficult to do in T-SQL.

If you remember
A * B = ANTILOG ( LOG (A) + LOG(B) )
So looking it at again, it is very straight forward using LOG, ANTILOG and SUM in SQL Server. (For doing ANTILOG you have EXP function in SQL Server.) Let me show you how :
Lets create some sample data first.

 

   
— Create sample table
CREATE TABLE Scores
(
ID INT IDENTITY,
Category VARCHAR(1),
)
GO
— Load sample data into table
INSERT INTO Scores(Category, Score)
SELECT ‘A’, 4 UNION ALL
SELECT
‘A’, 5 UNION ALL
SELECT ‘A’, 2 UNION ALL
SELECT
‘B’, 5 UNION ALL
SELECT
‘B’, 5 UNION ALL
SELECT
‘B’, 2 UNION ALL
SELECT
‘B’, 3
GO
— test the sample data
SELECT Id, Category, Score
FROM Scores
GO
 

The sample data :

 

ID CATEGORY SCORE
1 A 4
2 A 5
3 A 2
4 B 5
5 B 5
6 B 2
7 B 3
Multiplying Column Values:  And here is the query to calculate the Total Product of Score Column for each Category.
 

 

   
SELECT Category, SUM(Score) as TotalSum,EXP(SUM(LOG(Score))) as TotalProduct
FROM Scores
GROUP BY Category
Result :
 

 

Category TotalSum TotalProduct
A 11 40
B 15 150
Second column you have normal total SUM for the Score column and in 3rd column you have the total product of the Score column for each Category. 
Calculating The Running Multiplication:  After multiplying the column values, next question is how to calculate the Running Product similar to Running totals? See the following table –

 

ID Category Score RunningProduct
1 A 4 4
2 A 5 20
3 A 2 40
4 B 5 5
5 B 5 25
6 B 2 50
7 B 3 150
The logic is similar to what I just mentioned for Multiplying Column and adding the Running Total logic to query.
 

 

   
SELECT O.Id, O.Category, O.Score
        ,(  SELECT EXP ( SUM ( LOG (I.Score) ) )
             FROM Scores I
             WHERE I.Category = O.Category
             AND I.Id <= O.Id
         ) as RunningProduct
FROM Scores O
But remember this solution only works for positive numbers, if column contains negative values then you will need to modify the query using ABS function and taking into account number of negative values.  Well but at least you will get a start from this post.
 

 

http://th.wikipedia.org/wiki/ลอการิทึม