Solved

aggregate functions for the product of elements

Posted on 1998-08-28
4
200 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
0
Comment
Question by:danielmarkus
  • 2
  • 2
4 Comments
 
LVL 1

Author Comment

by:danielmarkus
ID: 1089826
Edited text of question
0
 
LVL 3

Accepted Solution

by:
gmoriak earned 100 total points
ID: 1089827
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
    begin
      if @curr is null select @curr = @col1, @product = 1
      if @curr <> @col1
          begin
            select @curr, @product
            select @product = 1, @curr = @col1
          end
      select @product = @product * @col2

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

select @curr, @product

close ggg

0
 
LVL 1

Author Comment

by:danielmarkus
ID: 1089828
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
0
 
LVL 3

Expert Comment

by:gmoriak
ID: 1089829
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.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

896 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now