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 - 2005-05-16 : 21:08:28

hey all I think I have asked a similar question before but cant seem to find the solution :)

just wondering how to multiply field values in a select statement?

i remember it had to do with exp and log functions just cant exactly get it out again any help would be great

EDIT: just on side note the values are all non null and greater than 0

------------------------------------------------------------------

Programming today is a race between software engineers striving to build bigger and better idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far, the Universe is winning.
Rich Cook

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2005-05-16 : 21:17:22

*

Damian
Ita erat quando hic adveni.

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2005-05-16 : 21:19:09

* works also

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2005-05-16 : 21:22:10

Damian
Ita erat quando hic adveni.

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2005-05-16 : 21:27:46

That would be subtraction Damian.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

Ex
Posting Yak Master

166 Posts

Posted - 2005-05-16 : 21:28:45

umm withouth sounding like an idiot can you please post an example

umm sorry i meant a column not fields :)

------------------------------------------------------------------

Programming today is a race between software engineers striving to build bigger and better idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far, the Universe is winning.
Rich Cook

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2005-05-16 : 21:32:09

WTF

USE Northwind

Select orderid * employeeid
from orders

Seriously though, is Books online not installed on your machine ?
When you open it, and look at the index, one of the top 20 entries is "* multiply".

What uni did you graduate from ?

Damian
Ita erat quando hic adveni.

FistyTheFerret
Starting Member

6 Posts

Posted - 2005-05-16 : 21:32:30

Programming today is a race between software engineers striving to build bigger and better idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far, the Universe is winning.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-05-16 : 21:59:48

Ex -- Just curious, do you really have a computer science degree ?

- Jeff

Ex
Posting Yak Master

166 Posts

Posted - 2005-05-16 : 22:09:51

ok lol i am sorry my question wasn't clear.
i know how to multiply columns :P i am not that stupid, i meant multiple the values in one column together like a sum would do

i.e

select sum(mycol) from mytable

but instead of a sum i want to multiply

so if i had column

mycol
5
4
3
2
1

answer would be 120

from memory i had a solution that uses the log and exp functions but cant get it to work again

------------------------------------------------------------------

Programming today is a race between software engineers striving to build bigger and better idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far, the Universe is winning.
Rich Cook

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2005-05-16 : 22:20:44

Do you need it in a set ? Or will a scalar do ?

DECLARE @Mult int
SELECT @Mult = 1

SELECT @Mult = @Mult * MyCol
FROM MyTable

SELECT @Mult

Damian
Ita erat quando hic adveni.

Ex
Posting Yak Master

166 Posts

Posted - 2005-05-16 : 22:27:12

set if possible. i know it can be done in a single select.
I have done it before, but another programmer deleted my old backup of the script :(

------------------------------------------------------------------

Programming today is a race between software engineers striving to build bigger and better idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far, the Universe is winning.
Rich Cook

Ex
Posting Yak Master

166 Posts

Posted - 2005-05-16 : 22:32:19

all good i worked it out

SELECT CAST( EXP(SUM(LOG(mycol))) AS integer) from mytable

well thats close enough for me to work with

thanks all

------------------------------------------------------------------

Programming today is a race between software engineers striving to build bigger and better idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far, the Universe is winning.
Rich Cook

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2005-05-16 : 22:34:01

119 is close enough to 120 .... cool

Damian
Ita erat quando hic adveni.

Ex
Posting Yak Master

166 Posts

Posted - 2005-05-16 : 22:34:39

oh and Jeff i do have a computer science degree,

no need to be harsh just because i didn't word my question right :P

------------------------------------------------------------------

Programming today is a race between software engineers striving to build bigger and better idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far, the Universe is winning.
Rich Cook

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-05-16 : 22:38:21

[code]
Create Table #seed (n int)
Insert Into #Seed
Select 1 union all Select 2 Union all Select 3 Union all
Select 1 union all Select 2 Union all Select 3
--Select 4 Union Select 5 Union Select 6 Union Select 7 Union Select 8 Union Select 9

SELECT convert(int,round(EXP(SUM(LOG(n))),0)) from #seed Where n <= 4

Drop Table #seed
[/code]

this should fix it right... by the way, this is pretty interesting.

Corey

Secret Service Agent: Mr. President, you're urinating on me.
President Lyndon Johnson: I know I am. It's my prerogative.

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2005-05-16 : 22:39:27

OR

SELECT CAST(CEILING( EXP(SUM(LOG(MyCol)))) as int)
from MyTable

Damian
Ita erat quando hic adveni.

Ex
Posting Yak Master

166 Posts

Posted - 2005-05-16 : 22:42:17

i would think this might be a function that would of been added in the aggregate functions guess its not that common after all :)

------------------------------------------------------------------

Programming today is a race between software engineers striving to build bigger and better idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far, the Universe is winning.
Rich Cook

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-05-16 : 23:21:52

Sorry, just making sure! I figured if you didn't learn how to multiply, something was wrong!

but I can see your needs are a little more complicated than it initially appeared, and to be honest I am quite relieved !!

- Jeff

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-05-16 : 23:45:33

You could also do this:


declare @n	int
declare @seed	table (n int)

insert into @seed (n)
select 1 union all select 2 union all
select 3 union all select 4 union all select 5

select @n = isnull(@n,1) * n from @seed

select Result = @n

Result      
----------- 
120

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources