How to
Multiply Column Values in SQL Server
We had a recent
discussion on the public newsgroups on how to get this requirement in a single
statement without using T-SQL. I was indeed taken by surprise how this
requirement was answered by fellow MVP's. Infact there is a solution even
though no so elegant, but works great having said what the requirement looks. So
take the following code and you will find it for
yourself.
Create table
MyTable (sample int)
GO
Insert into
mytable values (1)
Insert into
mytable values (2)
Insert into
mytable values (3)
Insert into
mytable values (4)
Insert into
mytable values (5)
So our example is
simple. We need to find the product of the column sample from the above
requirement. This though can be achieved with the dubious assignment syntax that
still works great as:
DECLARE
@prod INTEGER
SELECT @prod
= sample*COALESCE(@prod,1) FROM
MyTable
SELECT
@prod
But there are a number of arguements to why this will work or not
work. A simple search at the newsgroups will give you the pitfalls. Moreover for
people who use this syntax in SQL Server 2000 need to note that this works in
SQL Server 2000 Post SP2 only. This did turn to be prety easy. But the actual
result that stunned me is:
SELECT
CAST(ROUND(
COALESCE(EXP(SUM(LOG(
ABS(NULLIF(sample,0))))),0)
* SIGN(MIN(
ABS(sample)))
*
(COUNT(NULLIF(SIGN(sample),1))%2*-2+1)
,0) AS INTEGER) AS
product
FROM
MyTable
From the MVP who posted this solution, I had asked the logic behind
this. The answer was "Fundamentally it just exploits the fact that the logarithm
of the product is equal to the sum of the logs: LOG(a) + LOG(b) = LOG(a * b).
The SIGN(MIN()) and COUNT(*) expressions are there to handle negative values and
zeros correctly."
So I do see the potential that SET based solutions can offer. It is
quite powerful and even though the solutions might not be straight forward, the
solutions do work elagant and flawlessly.
|