multiply all values in column with loop

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

Transact-SQL (2000)

multiply all values in column with loop

Author

Topic

dirwin26
Yak Posting Veteran

81 Posts

Posted - 2006-09-06 : 12:11:35

I have a table with one column with the following data:

1
2
3

I want to loop through the table multipying all values together each time the loop count increases by one row.

The result should be:

1 (1X1)
2 (1X2)
6 (1X2X3)

i've played around with cursors and using coalesce, but i am getting all values multiplied together all at once, so my result is automatically 6.

Does anyone have any ideas on this?
Thanks

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-09-06 : 12:50:02

Here is one way:


--------------------------------------------
--create the data table
set nocount on
if object_id('junk') > 0 
	drop table junk
create table junk(rowid int identity(1,1) primary key clustered, n int)
insert junk (n)
select 1 union
select 2 union
select 3 union
select 4

--------------------------------------------
--create a function to get the product of all preceeding rows
if object_id('dbo.udfButt') > 0
	drop function dbo.udfButt
go
create function dbo.udfButt (@n int)
returns bigint
as
begin
	declare @bi bigint
	select @bi = coalesce(@bi * n, n) from junk where n <= @n order by rowid
	return @bi
end
go

--------------------------------------------
--use the function for each row
select n, x = dbo.udfButt(n) from junk order by rowid


output:
n           x                    
----------- -------------------- 
1           1
2           2
3           6
4           24

Be One with the Optimizer
TG

dirwin26
Yak Posting Veteran

81 Posts

Posted - 2006-09-06 : 15:50:55

the code only works for increasingly larger #'s that are successive.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-09-06 : 15:58:46

woops, I had added a [rowid] column for that possibility but never changed the function to use it.


--------------------------------------------
--create the data table
set nocount on
if object_id('junk') > 0 
	drop table junk
create table junk(rowid int identity(1,1) primary key clustered, n int)
insert junk (n) values (4)
insert junk (n) values (8)
insert junk (n) values (1)
insert junk (n) values (2)

--------------------------------------------
--create a function to get the product of all preceeding rows
if object_id('dbo.udfButt') > 0
	drop function dbo.udfButt
go
create function dbo.udfButt (@rowid int)
returns bigint
as
begin
	declare @bi bigint
	select @bi = coalesce(@bi * n, n) from junk where rowid <= @rowid order by rowid
	return @bi
end
go

--------------------------------------------
--use the function for each row
select n, x = dbo.udfButt(rowid) from junk order by rowid

output:
n           x                    
----------- -------------------- 
4           4
8           32
1           32
2           64

Be One with the Optimizer
TG

dirwin26
Yak Posting Veteran

81 Posts

Posted - 2006-09-06 : 16:18:29

thanks, that fixed it.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources