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!
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!
Comments
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
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 :)
http://sqlblogcasts.com/blogs/madhivanan/archive/2007/11/13/simple-multiply-function.aspx