• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 239
  • Last Modified:

aggregate functions for the product of elements


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

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,

  • 2
  • 2
1 Solution
danielmarkusAuthor Commented:
Edited text of question
You have to use a cursor - I hate cursors.  Instead of name and value - I used col1 and col2 - you can substitute as needed.

set nocount on

declare @col1 char (1), @col2 int, @curr char(1), @product int
declare ggg cursor for select col1, col2 from product order by col1
open ggg

select @col1 = NULL, @product = 0
fetch ggg into @col1, @col2
while @col1 is not NULL
      if @curr is null select @curr = @col1, @product = 1
      if @curr <> @col1
            select @curr, @product
            select @product = 1, @curr = @col1
      select @product = @product * @col2

      select @col1 = NULL
      fetch ggg into @col1, @col2

select @curr, @product

close ggg

danielmarkusAuthor 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
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.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now