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.
A * B = ANTILOG ( LOG (A) + LOG(B) ) |
— 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 |
SELECT Category, SUM(Score) as TotalSum,EXP(SUM(LOG(Score))) as TotalProduct FROM Scores GROUP BY Category |
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 |
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 |
http://sqlserver-qa.net/blogs/t-sql/archive/2007/11/13/2745.aspx
http://www.devx.com/tips/Tip/15396
http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/Q_10226918.html
http://www.eggheadcafe.com/software/aspnet/30291469/function-like-sum-but-m.aspx
http://www.bigresource.com/MS_SQL-Multiply-Rows-with-T-SQL-Tp8TG6lF.html
http://th.wikipedia.org/wiki/ลอการิทึม
Leave a Reply