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 rememberA * 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 |
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 |
Category | TotalSum | TotalProduct |
A | 11 | 40 |
B | 15 | 150 |
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 |
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.