Multiplication in select - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Multiplication in select

Author

Topic

Ex
Posting Yak Master

166 Posts

Posted - 2004-12-19 : 21:58:33

Hello all,

Just wondering if anyone knows if there is a function or possible how to do this

I would like to do something similar to

Select sum(id) from mytable

But I would like multiply the numbers instead of add

nr
SQLTeam MVY

12543 Posts

Posted - 2004-12-19 : 22:20:08

declare @i int
select @i = 1
Select @i = @i * id from mytable
select @i

why do you want to do this?

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Ex
Posting Yak Master

166 Posts

Posted - 2004-12-19 : 22:26:03

i am transfering a cursor into set based and trying to remove the need for the cursor which does this exact thing kinda stupid dont know why they made a cursor to do it, but i have to change it :) thanks for ya help

VIG
Yak Posting Veteran

86 Posts

Posted - 2004-12-19 : 23:46:38

select exp(sum(log(myField)))
from MyTable

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-12-20 : 03:48:22

using
select exp(sum(log(myField)))
from MyTable
needs some error handling:
log(0) is not defined
rounding errors can occur.

SELECT CAST(ROUND(COALESCE(EXP(SUM(LOG(ABS(nullif(col,0))))),0),0) AS INTEGER) AS output_value FROM @mytable
should do it.

Go with the flow & have fun! Else fight the flow

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources