Skip to main content

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!

Comments

Anonymous 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 :)
Unknown said…
Some other methods
http://sqlblogcasts.com/blogs/madhivanan/archive/2007/11/13/simple-multiply-function.aspx

Popular posts from this blog

Script table as - ALTER TO is greyed out - SQL SERVER

One of my office colleague recently asked me why we are not able to generate ALTER Table script from SSMS. If we right click on the table and choose "Script Table As"  ALTER To option would be disabled or Greyed out. Is it a bug? No it isn't a bug. ALTER To is there to be used for generating modified script of Stored Procedure, Functions, Views, Triggers etc., and NOT for Tables. For generating ALTER Table script there is an work around. Right click on the table, choose "Modify" and enter into the design mode. Make what ever changes you want to make and WITHOUT saving it right click anywhere on the top half of the window (above Column properties) and choose "Generate Change Script". Please be advised that SQL Server would drop actually create a new table with modifications, move the data from the old table into it and then drop the old table. Sounds simple but assume you have a very large table for which you want to do this! Then it woul

AWS fatal error: An error occurred (400) when calling the HeadObject operation: Bad Request

While using AWS and trying to copy a file from a S3 bucket to my EC2 instance ended up with this error message. Command Used: aws s3 cp s3://mybucketname/myfilename.html /var/www/html/ Error: fatal error: An error occurred (400) when calling the HeadObject operation: Bad Request The error goes off if we add the region information to the command statement. I am using Asia Pacific (Mumbai) so used ap-south-1 as the region name. Modified Command: aws s3 cp s3://mybucketname/myfilename.html /var/www/html/ --region ap-south-1

[Non Tech] Want to know the recipe for Omelette :)

Fed up with Bread - Jam and Curd Rice, today i wanted to eat Omelette. Interesting part is I wanted to cook it myself :) So in the first picture you see all the items which are needed for preparing an Omelette. When I had a closer look at the eggs I see that almost all the eggs are broken. But believe me when I bought it couple of days back it was in perfect condition! I was wondering whether the eggs have become rotten or pretty old to consume! I tried taking an egg and break it but couldn't break it at all :) Since I have kept in the freezer all the eggs have frozen and looked like a iron ball :) After trying for few minutes of trying i removed the shell of the egg and then kept that iron ball :) into a bowl and placed it within Oven. I heated it for 1 minute and checked. It melted only to a limit. So i just set it for another 2 minutes and checked it later. It has melted but the part of the egg white has become a Omelette :( I didn't leave it there. I took the bowl out of