Is there a PRODUCT() function in SQL?

Hi,

Is there a function that I can use to calculate the product of values across rows for a particular field. I want the equivalent of SUM() for multiplication. If there isn't a built in function, can anyone think of a way to calculate this product without iterating over every row in a loop? Yikes!

As an example, if I have the following table:

avalue
2
4
3
3

I want to calculate the product of avalue, which is 2*4*3*3 = 72.

TIA

There is no official function, but many databases implement their own functions to add to what SQL provides. What database system are you using?

I am not sure how you could do this in just SQL. I would try and tackle this in the code

I'm using MySQL v3.23.46. I think for now, I will just use something like:

$sth = execSQL("SELECT * FROM daily_returns");
$totReturnPlan *= ($ref->{DailyReturn} + 1) while($ref = $sth->fetchrow_hashref());

Unless you have something more efficient...

I know that it can be done in MSSQL by issuing something like:

declare @x int
set @x = 1
select @x = @x * (DailyReturn + 1) from daily_returns
print @x

Thanks

if you're using SQL Server 2k, create a user defined function to calculate a product.

Just about any decent DBMS can have user-defined functions or procedures, including (of course) Oracle, DB2, PostgreSQL, Interbase/Firebird, SAPDB, etc....

Actually, even MySQL has a method for doing this also, although it is more of a work-around than a true feature.

And there is definitely something to be said for confining this sort of logic to the database, rather than the inefficiency of retrieving all the numbers into your application before making the calculation.

Thanks for your ideas, I am especially interested in getting MySQL to handle UDFs...I'll check that out.