new type of aggregate function (product function)
Author Message new type of aggregate function (product function)

Just in the way one can use sum, avg, count etc.. as
aggregate functions in a query, I am trying to create
a "product" function.  (one that multiplies the numbers
across a domain).  So, for example:

id  number
232      2
232      3
232      2
876      3
876      1
876      7

would result:

id     number
232      12
876      21

--Andre

Sat, 31 Jul 2004 03:50:10 GMT  new type of aggregate function (product function)
Where's Duane when you need him?

To answer the question, just apply a math equivalent using an
expression like:

Exp(Sum(Log([number]))) AS product

On Mon, 11 Feb 2002 11:50:10 -0800, "Andre"

Quote:

>Just in the way one can use sum, avg, count etc.. as
>aggregate functions in a query, I am trying to create
>a "product" function.  (one that multiplies the numbers
>across a domain).  So, for example:

>id  number
>232      2
>232      3
>232      2
>876      3
>876      1
>876      7

>would result:

>id     number
>232      12
>876      21

>--Andre

Sat, 31 Jul 2004 04:08:33 GMT  new type of aggregate function (product function)
Oh man, I shoulda know that!!   Thanks Marshall.

Quote:
>-----Original Message-----
>Where's Duane when you need him?

>To answer the question, just apply a math equivalent
using an
>expression like:

>Exp(Sum(Log([number]))) AS product

>On Mon, 11 Feb 2002 11:50:10 -0800, "Andre"

>>Just in the way one can use sum, avg, count etc.. as
>>aggregate functions in a query, I am trying to create
>>a "product" function.  (one that multiplies the numbers
>>across a domain).  So, for example:

>>id  number
>>232      2
>>232      3
>>232      2
>>876      3
>>876      1
>>876      7

>>would result:

>>id     number
>>232      12
>>876      21

>>--Andre

>.

Sat, 31 Jul 2004 04:30:03 GMT  new type of aggregate function (product function)

Marsh

You may be too fast for Duane.

Actually, I prefer a slight modification in this case to ensure the Values
are whole numbers, not decimals.

CLng( Exp( Sum( Log( [number] ) ) ) ) AS Product

Cheers
Van

Quote:
> Where's Duane when you need him?

> To answer the question, just apply a math equivalent using an
> expression like:

> Exp(Sum(Log([number]))) AS product

> On Mon, 11 Feb 2002 11:50:10 -0800, "Andre"

> >Just in the way one can use sum, avg, count etc.. as
> >aggregate functions in a query, I am trying to create
> >a "product" function.  (one that multiplies the numbers
> >across a domain).  So, for example:

> >id  number
> >232      2
> >232      3
> >232      2
> >876      3
> >876      1
> >876      7

> >would result:

> >id     number
> >232      12
> >876      21

> >--Andre

Sat, 31 Jul 2004 07:58:00 GMT  new type of aggregate function (product function)
That's a good idea Van, especially considering how much floating point
is going on in there.

Marsh

On Tue, 12 Feb 2002 10:58:00 +1100, "Van T. Dinh"

Quote:

>Actually, I prefer a slight modification in this case to ensure the Values
>are whole numbers, not decimals.

>CLng( Exp( Sum( Log( [number] ) ) ) ) AS Product

>> Where's Duane when you need him?

>> To answer the question, just apply a math equivalent using an
>> expression like:

>> Exp(Sum(Log([number]))) AS product

>> On Mon, 11 Feb 2002 11:50:10 -0800, "Andre"

>> >Just in the way one can use sum, avg, count etc.. as
>> >aggregate functions in a query, I am trying to create
>> >a "product" function.  (one that multiplies the numbers
>> >across a domain).  So, for example:

>> >id  number
>> >232      2
>> >232      3
>> >232      2
>> >876      3
>> >876      1
>> >876      7

>> >would result:

>> >id     number
>> >232      12
>> >876      21

Sat, 31 Jul 2004 11:44:45 GMT

 Page 1 of 1 [ 6 post ]

Relevant Pages