Query issue

[RatiKanta pal]

HI,
I know this not the forum for this but still need some idea.

I want to write a query which will return multiplication for of all column value.

For example:

Table
-------------------
A B C
1
2
2
3
1
--------------------
12

this my expected result how to write query for this??

[Martin Vashko]

Oracle doesn't have such an aggregation function.

Assuming your values are always positive, you could employ a little trick:select exp(sum(ln(column))) from table; This might introduce inaccuracies, though, due to the ln and exp functions.

Another possibility would be to create the aggregation function yourself. It's not really trivial, though. All the gory details (including the exp(sum(ln(x))) trick) can be found on AskTom.

If I had to create a one-off query returning a product, I'd probably do it in a stored procedure. If it was needed more than, say, three times in the project, I'd probably try to implement the function as described on AskTom.

[chris webster]

Martin Vajsar wrote:Assuming your values are always positive, you could employ a little trick:select exp(sum(ln(column))) from table;

Nice trick!