Multiplying Column Values

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 forums - How 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 at it 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 3^rd 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 Rows 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.