Thursday, June 4, 2009

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 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 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.

5 comments:

  1. Hi buddy, I read ur concepts about multiplying the columns but I want something different related to multiply two columns.
    This 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

    ReplyDelete
  2. how to multiply two columns in two different tables?

    ReplyDelete
  3. Hi Venugopala,
    You 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

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

    i 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

    ReplyDelete
  5. I like this portal.. It helped me immensely..
    Thank you very much for posting solutions here....

    ReplyDelete