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

General SQL Server Forums

New to SQL Server Programming

multiply

Author

Topic

maya_zakry
Constraint Violating Yak Guru

379 Posts

Posted - 2007-05-15 : 05:12:10

hi all
i want to multiply each row, does anyone has more simple solution other than using cursor???


-- Prepare sample data
DECLARE	@tbl1 TABLE (num1 float )

INSERT	@tbl1
SELECT	'1'  UNION ALL
SELECT	'2'  UNION ALL
SELECT	'3'

select * from @tbl1 

declare @no1 as float, @total as float
set @total=1

DECLARE c1 CURSOR FOR
	select * from @tbl1 
	OPEN c1
	
	FETCH NEXT FROM c1
	INTO  @no1
	
	WHILE @@FETCH_STATUS = 0
	BEGIN
		set @total=@total*@no1
		
	FETCH NEXT FROM c1
	INTO  @no1
	END
	
	CLOSE c1
	DEALLOCATE c1

select @total AS SubMultiply

~~~Focus on problem, not solution ¯\(º_o)/¯ ~~~

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-05-15 : 05:26:14

[code]
select @total = isnull(@total, 1) * num1 from @tbl1
select @total
[/code]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-05-15 : 06:00:28

select exp(sum(log(num1))) from @tbl1

(if all values are positive)

Peter Larsson
Helsingborg, Sweden

maya_zakry
Constraint Violating Yak Guru

379 Posts

Posted - 2007-05-15 : 07:09:22

peter.. incredible u are (yes all ~~=ve)~~-ve)
khtan.. how come i get 36?

~~~Focus on problem, not solution ¯\(º_o)/¯ ~~~

maya_zakry
Constraint Violating Yak Guru

379 Posts

Posted - 2007-05-15 : 07:13:46

but peter like usual ive no idea what it is all about...
i mean log(num1) giving 0.69314718055994529 and exp(num1) givoing me 2.7182818284590451????
huwaaa huwaaaa

~~~Focus on problem, not solution ¯\(º_o)/¯ ~~~

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-05-15 : 07:17:14

quote:
Originally posted by maya_zakry

peter.. incredible u are (yes all ~~=ve)~~-ve)
khtan.. how come i get 36?

~~~Focus on problem, not solution ¯\(º_o)/¯ ~~~

because you append my script to the back of yours and run. Initialize the @total = NULL or 1 before running mine.

maya_zakry
Constraint Violating Yak Guru

379 Posts

Posted - 2007-05-15 : 08:02:16

oh yeaa.. yeayy.. tan it worked...! my mistake.. thankss// and is understamdable to my standard.. hihihi.. so ill use this one!

~~~Focus on problem, not solution ¯\(º_o)/¯ ~~~

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-05-15 : 09:03:41

quote:
Originally posted by maya_zakry

oh yeaa.. yeayy.. tan it worked...! my mistake.. thankss// and is understamdable to my standard.. hihihi.. so ill use this one!

~~~Focus on problem, not solution ¯\(º_o)/¯ ~~~

You need to change your signature as I told you in the other thread

Madhivanan

Failing to plan is Planning to fail

maya_zakry
Constraint Violating Yak Guru

379 Posts

Posted - 2007-05-16 : 21:00:36

hi all..
back here again..
one of my friend ask can this solution work with access.. i know this kind of out of topic.. but maybe somebody know..

~~~Focus on problem, not solution ¯\(º_o)/¯ ~~~

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-05-16 : 21:56:19

I don't know Access. But why don't you ask your fried to give it a try on Access

. Don't worry, it won't bite.

maya_zakry
Constraint Violating Yak Guru

379 Posts

Posted - 2007-05-16 : 22:52:30

that's why.. i dont have access.. the last time i use access was when i first know what is DB.. and that was about 4 yrs ago.. i wonder how to write SP in access..

~~~Focus on problem, not solution ¯\(º_o)/¯ ~~~

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-05-17 : 00:26:56

SP is not available in Access

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-05-17 : 02:35:57

Sort of.
You can write a VBA procedure for this. Or VBA function.
But better still, write a select query and use the code I sugggested.

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources