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

Registry manipulation from SQL

Registry Manupulation from SQL Server is pretty easy. There are 4 extended stored procedure in SQL Server 2000 for the purpose of manupulating the server registry. They are: 1) xp_regwrite 2) xp_regread 3) xp_regdeletekey 4) xp_regdeletevalue Let us see each one of them in detail! About xp_regwrite This extended stored procedure helps us to create data item in the (server’s) registry and we could also create a new key. Usage: We must specify the root key with the @rootkey parameter and an individual key with the @key parameter. Please note that if the key doesn’t exist (without any warnnig) it would be created in the registry. The @value_name parameter designates the data item and the @type the type of the data item. Valid data item types include REG_SZ and REG_DWORD . The last parameter is the @value parameter, which assigns a value to the data item. Let us now see an example which would add a new key called " TestKey ", and a new data item under it called TestKeyValue :

Screen scraping using XmlHttp and Vbscript ...

I wrote a small program for screen scraping any sites using XmlHttp object and VBScript. I know I haven't done any rocket science :) still I thought of sharing the code with you all. XmlHttp -- E x tensible M arkup L anguage H ypertext T ransfer P rotocol An advantage is that - the XmlHttp object queries the server and retrieve the latest information without reloading the page. Source code: < html > < head > < script language ="vbscript"> Dim objXmlHttp Set objXmlHttp = CreateObject("Msxml2.XMLHttp") Function ScreenScrapping() URL == "UR site URL comes here" objXmlHttp.Open "POST", url, False objXmlHttp.onreadystatechange = getref("HandleStateChange") objXmlHttp.Send End Function Function HandleStateChange() If (ObjXmlHttp.readyState = 4) Then msgbox "Screenscrapping completed .." divShowContent.innerHtml = objXmlHttp.responseText End If End Function </ script > < head > < body > &l

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