Function like SUM() but multiply

Is there a function that I can use to multiply all of the values of a column
from a result set together? Much like SUM() adds all the values together?
Or am I going to have to just use AVG() and COUNT() and multiply those 2
values together?
thnx,
Christoph
Christoph,
http://sqljunkies.com/WebLog/mosha/archive/2006/12/18/aggregate_multiplication.aspx
RLF
"Christoph Boget" <jcboget@.yahoo.com> wrote in message
news:O63$fDkwHHA.4132@.TK2MSFTNGP02.phx.gbl...
> Is there a function that I can use to multiply all of the values of a
> column from a result set together? Much like SUM() adds all the values
> together? Or am I going to have to just use AVG() and COUNT() and multiply
> those 2 values together?
> thnx,
> Christoph
>
|||On Mon, 9 Jul 2007 11:50:24 -0400, Christoph Boget wrote:

>Is there a function that I can use to multiply all of the values of a column
>from a result set together? Much like SUM() adds all the values together?
>Or am I going to have to just use AVG() and COUNT() and multiply those 2
>values together?
>thnx,
>Christoph
>
Hi Christoph,
If you're on SQL Server 2005, you can write a custom aggregate for this.
However, the technique below, which works on all versions of SQL Server,
is probably faster (though not really easy to understand for the novice
coder, and requiring some maths skills to graps as well).
(Simple version - use only if you know for sure that all values are > 0)
SELECT Grp, POWER(10.0, SUM(LOG10(Value))) AS Product
FROM YourTable
GROUP BY Grp;
(Advanced version - handles 0 and <0 values gracefully)
SELECT Grp,
CASE
WHEN MAX(CASE WHEN Value = 0 THEN 1 END) = 1 THEN 0
ELSE CASE
WHEN COUNT(CASE WHEN val < 0 THEN 1 END) % 2 = 0
THEN 1 ELSE -1
END * POWER(10.0, SUM(LOG10(NULLIF(ABS(Value),0))))
END AS Product
FROM YourTable
GROUP BY Grp;
(Advanced version, written in an even more incomprehensible way but
maybe a bit faster)
SELECT Grp,
CAST(ROUND(EXP(SUM(LOG(ABS(NULLIF(Value,0)))))*(1-SUM(1-SIGN(Value))%4)*(1-SUM(1-SQUARE(SIGN(val)))),0)
AS INT) AS Product
FROM YourTable
GROUP BY Grp;
These techniques are all described and explained in Itzik Ben-Gan's book
"Inside Microsoft SQL Server 2005 T-SQL Querying", page 358-360.
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis