SQLServerCentral » SQL Server 2005 » SQL Server 2005 General Discussion » Product of Values in a ColumnInstantForum 2016-2 FinalSQLServerCentralhttps://www.sqlservercentral.com/Forums/SQLServerCentralThu, 19 Jan 2017 04:11:40 GMT20Product of Values in a Columnhttps://www.sqlservercentral.com/Forums/FindPost612381.aspxI 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 =)Tue, 02 Dec 2008 14:36:49 GMTbkmooneyRE: Product of Values in a Columnhttps://www.sqlservercentral.com/Forums/FindPost612404.aspxPre-Algebra to the rescue...
Thanks!Tue, 02 Dec 2008 14:36:49 GMTbkmooneyRE: Product of Values in a Columnhttps://www.sqlservercentral.com/Forums/FindPost612392.aspxThere'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.
LOG[sub]x[/sub](a * b * c) = LOG[sub]x[/sub](a) + LOG[sub]x[/sub](b) + LOG[sub]x[/sub](c)
So...
[code]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 [/code]
Result: 120Tue, 02 Dec 2008 14:22:32 GMTGilaMonster