MySQL how to multiply single columns values with a specific id in a function..

[Scooby08]

Hello..

 

I'm just wondering if there is a way to multiply a single columns values using a stored function..

 

Example table: (my_table)

 

id    my_id    multiply

0      1            2

1      1            2

2      3            2

3      3            2

4      3            2

5      3            2

 

Now I will never know what the multiply number will be.. I'm using 2 as an example..

 

Here's pretty close to what I was thinking, but I just don't know how to multiply the fields.. Do I need to run a loop or something like that?

 

CREATE FUNCTION multiply(in_my_id INT) 
RETURNS DECIMAL(10,2)
BEGIN
DECLARE total DECIMAL(10,2);
SET total = (SELECT multiply FROM my_table WHERE my_id = in_my_id); -- loop here??
RETURN total;
END;

     

If in_my_id = 1, then it should return 4..

If in_my_id = 3, then it should return 16..

 

Thanks for any replies!

[Scooby08]

Found my answer finally.. Thanks..

 

CREATE FUNCTION multiply(in_my_id INT) 
RETURNS DECIMAL(10,2)
BEGIN
DECLARE total DECIMAL(10,2);
SET total = (SELECT EXP(SUM(LOG(COALESCE(multiply,1)))) FROM my_table WHERE my_id = in_my_id);
RETURN total;
END;

 

http://lists.mysql.com/mysql/166184

 

 

[Scooby08]

I also found out that LOG is not defined for negative values..

 

http://forums.mysql.com/read.php?117,219696,264407#msg-264407

[fenway]

That's a math issue, not a mysql issue. 

[Scooby08]

Well then move it..

[fenway]

Well then move it..

There's no "log of a negative number is imaginary" forum.