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 |

**Result**:

Category | TotalSum | TotalProduct |

A | 11 | 40 |

B | 15 | 150 |

^{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 |

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.

Hi buddy, I read ur concepts about multiplying the columns but I want something different related to multiply two columns.

ReplyDeleteThis is just simple TestDB

Table name Test

3 Columns

Value1,Value2,Value3

Value1 and Value are having any values like 1 to 10 in Value1 and 21 to 30 in Value to. Now the the query is I want multiplication of Value1 and Value2 in Value3. eg. 1 * 21 = 21, 2 * 22 =44, etc. plus I want to make sum of all values in Value3 as a total. Can u do that for me? Its urgently required for my project. Can I add u in gtalk to chat? I m also from Pune

how to multiply two columns in two different tables?

ReplyDeleteHi Venugopala,

ReplyDeleteYou can do it like this

SELECT T1.ColA * T2.ColB as MultiColAColb

FROM TableA as T1 JOIN TableB as T2

ON T1.id = T2.id

sorry didnt get any place to ask u a que. so i m submiting my query here...

ReplyDeletei want to implement the following thing to make my project looked like welled equiped...

i need some help ... in the follwing things...

1. want to implement registration key process [name: , serial key feature in my application]

2. how can i kno the mac id of any pendrive and use it in my (csharp) application.so that i can take backup of any data when only the registered pendrive (with application) attach with the machine.

3. how can i use sql db using apps.config from network , means how can i run my application using the network database.

4. how can i design(Logic) a timetable application.

5. n finally without using sql server (installed in the user machine) how can i install sql db file embaded with my application installation, so that i can use it after installation complete.

Hope will find some responce on the same .

Thnks in advance.

Regards,

Praveen

rocking[dot]praveen@gmil[dot]com

I like this portal.. It helped me immensely..

ReplyDeleteThank you very much for posting solutions here....