We help IT Professionals succeed at work.

aggregate functions for the product of elements

danielmarkus
danielmarkus asked
on
Medium Priority
248 Views
Last Modified: 2010-03-19
Hi,

I've a problem with aggregate functions. Is there similar to SUM an other function that results in the product the element.

table
name value
a     2
a     3
b     3
b     4

SELECT name, sum(value) FROM Table GROUP BY name
results in
name  sum of value
a       5
b       7

I'm looking for a function that will result in
name  prduct of value
a       6   (i.e. 2 * 3)
b       12  (i.e. 3 * 4)

Is there a function for it? or do I've to do it with cursors and other programming techniques?

I'm quite desperate for the answer.

Oh, and there is no other way to organise the tables, so please don't give plain suggestions in that direction.
The database calculates the price of elements. This price consists of the product of varying attributes which are in a child table.

Any help will be highly regarded,

Daniel
Comment
Watch Question

Author

Commented:
Edited text of question
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
I guess your answer works. Thanks, However, I think this answer is better. Perhaps you can use it:

SELECT name, EXP(SUM(LOG(value)))
FROM myTable
GROUP BY name

Commented:
Next time I need to do a product (which will be my first) I'll be sure to use it.  I told you I hate cursors.  It's a cop out to use them, but I slept through math during log/exp.
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.