|
|
|
Valued Member
      
Group: General Forum Members
Last Login: Wednesday, August 29, 2012 2:08 PM
Points: 74,
Visits: 237
|
|
I am looking for a function that multiplies values in a column the way that the function sum adds them.
In MS Excel, this function is called PRODUCT, so I expected there to be a similar function in MS SQL, but can't seem to find it.
Any help is greatly appreciated. Thanks =)
|
|
|
|
|
SSC-Dedicated
           
Group: General Forum Members
Last Login: Today @ 12:31 PM
Points: 38,091,
Visits: 30,384
|
|
There's no built-in function that does that. However...
Itzik, in his PASS precon, showed a cool trick for faking one using some highschool maths.
LOGx(a * b * c) = LOGx(a) + LOGx(b) + LOGx(c)
So...
DECLARE @Test TABLE (val int)
INSERT INTO @test VALUES (1) INSERT INTO @test VALUES (2) INSERT INTO @test VALUES (3) INSERT INTO @test VALUES (4) INSERT INTO @test VALUES (5)
SELECT POWER(10., SUM(LOG10(val))) FROM @Test Result: 120
Gail Shaw Microsoft Certified Master: SQL Server 2008, MVP SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
We walk in the dark places no others will enter We stand on the bridge and no one may pass
|
|
|
|
|
Valued Member
      
Group: General Forum Members
Last Login: Wednesday, August 29, 2012 2:08 PM
Points: 74,
Visits: 237
|
|
Pre-Algebra to the rescue...
Thanks!
|
|
|
|