467,923 Members | 1,853 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 467,923 developers. It's quick & easy.

Computing the product of a column groupped by...

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
  • viewed: 1308
Share:
3 Replies
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

"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
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.

Similar topics

reply views Thread by Joan MacEachern | last post: by
2 posts views Thread by Josué Maldonado | last post: by
14 posts views Thread by Aaron Watters | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.