Thursday, June 01, 2006

Finding the product of a column ...

There isn't a prebuild function for finding the product of a field in SQL Server. But there is a workaround which we could make use of by using the combination of SUM(), POWER() and LOG10() functions. If not for this query we would have to fetch all the records to the front-end and then do the calcuation there or use cursors to parse the records and write the logic for multiplying within that procedure. I have tested it very long time ago with both SQL Server 7.0 and 2000.

Steps to be followed for finding the product of a column are given below:

1. Find the LOG value of all the data
2. Add all the LOG values
3. With the help of POWER function return the SUM to the POWER 10
4. Add one to the final result.

That's it. You would have all the column values being multiplied and returned as an output. Let us try and implement it with an example.

Creating and populating a sample table

a. Create a test table

Create table TblTransaction
(
[CustomerName] varchar(20),
[Price] int,
[tax] int
)

b. Populate some test data into the above table

Insert into TblTransaction Values ('Vadivel', 10, 2)
Insert into TblTransaction Values ('Sneha', 25, 2)

Solution

The code snippet for finding the product of the field "price" is given below:

Select Power(10, Sum(Log10(price))) + 1 from TblTransaction

The code snippet for finding the product of two column (price * Tax ) is given below:

-- Declaring variables to hold the intermediate results
Declare @logTax float
Declare @logPrice float
Declare @sumLog float

Select @logTax = log10(tax), @logPrice = log10(price) from TblTransaction
Select @sumLog = @logTax + @logPrice
Select power(10, @sumLog) + 1

The above code sninppet or logic would work only for non-zero positive integer values and won't work for columns which has decimal values. For instance, if in our test table if the value of price is 10.5 and 25.5 the result would still show 249 and not the actual 267.75

Hope this helps!

3 comments:

Varalakshmi said...

I tried implementing the same for finding the product using the workaround you have specified. But It works fine only with few set of datas.

For example,

Select Power(10, Sum(Log10(price))) + 1 from TblTransaction

holds good for number pairs such as (55,10),(10,5) etc., whereas it
fails for (25,2)etc.,

The another example to find the product of two different columns also fails for few examples.Moreover, we are able to find the product of the datas only for the last row.

Can't it be found by

Select (tax*price) from TblTransaction

Where it lists the product of datas row wise.
If i am wrong any where please do correct me.

Thanks,
Varalakshmi

Vadivel said...

1. Yes true varalakshmi it fails for some numbers :)

2. Regarding "why we can't use Price * Tax directly" ---- Yeah that's how we normally do it. I was just trying to simulate "multiplication" using the built-in Sum(), Power() and Log10() functions. [Hope now you would understand how free i am at home :) ]

To my knowledge no where in a real world scenario we would be using this logic at all :)

Madhivanan said...

Some other methods
http://sqlblogcasts.com/blogs/madhivanan/archive/2007/11/13/simple-multiply-function.aspx