By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,589 Members | 2,255 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,589 IT Pros & Developers. It's quick & easy.

Computing the product of a column groupped by...

P: n/a
I need your help here :

I need to compute the product of a column, with conditions, over a
table, grouped by a key (it is a probability table).

In fact I need the exact equivalent of :

select key1, key2, sum( val )
from "sometable"
where "some parameterized condition"
group by key1, key2

*except* that I want the *multiplication* of all "val" instead of the sum.

I'd like the output to behave as much as a table column aggregative
function as possible, it is used in other queries.

In Oracle I'd solve this by using pipelined table functions, in DB2 I'm
lost, since table functions can only return the result of a select.

Can anyone help me ?

Am I stuck with using a procedure storing large results sets in a
temporary table with all the concurrency problems that this causes ?

Using DB2 for windows / UNIX 8.1.2.

Thanks in advance for any suggestion.

Mar 28 '06 #1
Share this Question
Share on Google+
3 Replies


P: n/a
My two ideas.
1) EXP(SUM(LOG(val)))
But, this returns floating point number. So, exact value could not be
received.

2) Use recursive query. (and common table expression, if you want
combine with other data)

Mar 28 '06 #2

P: n/a

"Tonkuma" <to*****@jp.ibm.com> a écrit dans le message de news:
11**********************@g10g2000cwb.googlegroups. com...
My two ideas.
1) EXP(SUM(LOG(val)))
But, this returns floating point number. So, exact value could not be
received.


Stupid me, I should have thought of this.

I like this idea, I'll check if there is not too much imprecision in the
result, I think it should be OK. It's probabilities anyway.

thanks !
Mar 28 '06 #3

P: n/a
Tonkuma wrote:
My two ideas.
1) EXP(SUM(LOG(val)))
But, this returns floating point number. So, exact value could not be
received.

2) Use recursive query. (and common table expression, if you want
combine with other data)


Another alternative is to roll your own aggregates. But this is not
trivial: http://tinyurl.com/7z49y and for groupings:
http://tinyurl.com/b2rq5

--
Knut Stolze
DB2 Information Integration Development
IBM Germany
Apr 3 '06 #4

This discussion thread is closed

Replies have been disabled for this discussion.