Multiplication - 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

Author

Topic

Ex
Posting Yak Master

166 Posts

Posted - 2005-01-06 : 19:02:10

Hey all

thanks to spirit1
from one of my previous posts i got this forumal to Multipliy all the values in the col of a table

SELECT CAST(ROUND(COALESCE(EXP(SUM(LOG(ABS(nullif(columnName,0))))),0),0) AS INTEGER) FROM mytable where ....

the problem is the nullif method

if i had the col values
5
0
5
answer would be = 25 and not 0 like it should be

i need to do it in a formula as the rest will be used in a update

UPDATE aTable SET aCol= /*Multiplication*/
WHERE etc......

any ideas?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-01-17 : 06:02:48

Try this



Declare @t table(no numeric)
insert into @t values(1)
insert into @t values(2)
insert into @t values(0)
insert into @t values(4)
insert into @t values(5)
declare @n numeric
set @n=1
select @n=@n * (Select case when T.no=0 then 1 else T.no end from @t where no=T.No) from @t T
select @n

Madhivanan

RM
Yak Posting Veteran

65 Posts

Posted - 2005-01-17 : 06:56:02

Just a small change...

Declare @t table(num numeric)
insert into @t values(5)
insert into @t values(0)
insert into @t values(5)

declare @n numeric

set @n=1
select @n=@n * (case when isnull(T.num,0)=0 then 1 else T.num end)
from @t T
select @n

--OR--

SELECT CAST(ROUND(COALESCE(EXP(SUM(LOG(ABS(case when isnull(num,0)=0 then 1 else num end)))),0),0) AS INTEGER)
from @t T

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources