Post reply

Product of Values in a Column

  • December 2, 2008 at 2:04 pm

    #383501

    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 =)

  • December 2, 2008 at 2:22 pm

    #906506

    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-2 TABLE (val int)

    INSERT INTO @test-2 VALUES (1)

    INSERT INTO @test-2 VALUES (2)

    INSERT INTO @test-2 VALUES (3)

    INSERT INTO @test-2 VALUES (4)

    INSERT INTO @test-2 VALUES (5)

    SELECT

    POWER(10., SUM(LOG10(val)))

    FROM @test-2

    Result: 120

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    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

  • December 2, 2008 at 2:36 pm

    #906515

    Pre-Algebra to the rescue...

    Thanks!

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply