Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Product of Values in a Column Expand / Collapse
Author
Message
Posted Tuesday, December 2, 2008 2:04 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, March 10, 2014 7:19 AM
Points: 83, Visits: 251
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 =)
Post #612381
Posted Tuesday, December 2, 2008 2:22 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 1:13 PM
Points: 40,428, Visits: 36,879
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

Post #612392
Posted Tuesday, December 2, 2008 2:36 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, March 10, 2014 7:19 AM
Points: 83, Visits: 251
Pre-Algebra to the rescue...

Thanks!
Post #612404
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse