SQL Server Performance

Equivalent of SUM()

Discussion in 'General Developer Questions' started by frettmaestro, Aug 31, 2003.

  1. frettmaestro New Member

    Is there a function in T-sql that works in the same manner as the SUM() but multiplies the values in the statement instead of adding them together? Say I have values in a table like this:
    ID Number
    1 2
    2 4
    3 6
    SELECT SUM(Number) FROM table would offcourse return 12 in this case but I need to return 48 (2 * 4 * 6). Anyone?

    --
    Frettmaestro
    "Real programmers don't document, if it was hard to write it should be hard to understand"
  2. bambola New Member

    Don't know if there's a build in function, but this should work.

    declare @t table(id int, number int)
    insert into @t select 1, 2 union all select 2, 4 union all select 3, 6
    declare @x int
    select @x = 1
    select @x = @x * number from @t
    select @x

    You might want to check for NULLs or ignore 0.

    Bambola.
  3. frettmaestro New Member

    Well...I was sort of hoping for something more elegant, but something like this is they way I have done it so far. But thanx... <img src='/community/emoticons/emotion-1.gif' alt=':)' /><br /><br />--<br />Frettmaestro<br />"Real programmers don't document, if it was hard to write it should be hard to understand"
  4. Argyle New Member

  5. frettmaestro New Member

    That's more like it!! Thanx <img src='/community/emoticons/emotion-1.gif' alt=':)' /> It seems like either the log or the exp uses aproximation but casting it to a decimal-datatype it worked excellent! This is what I did:<pre>declare @MyTable table (myNumber decimal (19, <img src='/community/emoticons/emotion-11.gif' alt='8)' />)<br />insert into @MyTable (myNumber) VALUES(2.5)<br />insert into @MyTable (myNumber) VALUES(3)<br />insert into @MyTable (myNumber) VALUES(5)<br /><br />select CAST(exp(sum(log(myNumber))) AS decimal(19, <img src='/community/emoticons/emotion-11.gif' alt='8)' />) from @MyTable</pre><br /><br />--<br />Frettmaestro<br />"Real programmers don't document, if it was hard to write it should be hard to understand"

Share This Page