Calculating the PRODUCT of a field using the GROUP BY clause...? 1

[marklenel]

Is it possible to calculate the PRODUCT of field values as they are combined using a GROUP BY clause? For example:

MyID MyValue
1 2
1 3
1 3
2 2
2 3
2 1

SELECT MyID, PRODUCT(MyValue) AS MyValue2
FROM MyTable
GROUP BY MyID;

To give
MyID MyValue2
1 18
2 6


So far I can only do it by taking the SUM of the logarithms i.e. LN(MyValue), then exponentiating these back out. I'd much rather do it in one step i.e. the select statement rather than add a couple of new steps of additional calculations.

Any thoughts gratefully received!

 

[rac2]

You can use an aggregate function in an expression, you can exponentiate the sum of the logs in the select statement.

SELECT MyID, EXP(SUM(LOG(MyValue))) AS Product
FROM MyTable
GROUP BY MyID

See also

http://dev.mysql.com/doc/mysql/en/Mathematical_functions.html

 

[ericbrunson]

Very clever math, I'm tickled.

I'm giving you a star for having Mad Skillz...

 

[marklenel]

That's very nice rac2 - thank you - but I'm going to have to hold half of your star in reserve I'm afraid...

When MyValue is zero or negative, LOG(MyValue) returns an error. How would you find the product of e.g. -2, +3, -4 and -5 (with a result of -120)?

 

[Bastien]

I think it might be getting a little to complex for sql. I would let the application code handle this.

Bastien

Cat, the other other white meat