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!