Selecting Multiple Aggregates

From: <dili66_at_my-deja.com>
Date: Fri, 13 Oct 2000 16:04:31 GMT
Message-ID: <8s7bq8$q10$1@nnrp1.deja.com>

I was trying to write a sql statement that would create several aggregates based on some conditions in the fields of some rows.

All the aggregates are using the same group by fields but different where clauses.

For example if the basic table is:

A B C

a1 b1 c2
a1 b1 c2

a1 ...
a2 ...
a2 ...
a2 ...

We would like a result

A COUNT1 COUNT2 COUNT3

a1   1      2      3
a2   2      2      4

...

which is produced as a "group by A" but the counts depend on conditions based on the other fields. For example for COUNT1 the condition could be "B=B1 AND C=c2" , for COUNT2 it could be "B<b3 OR C=c2" etc

The only way I could figure out doing this was to have different subqueries each one computing one of the aggregate columns and then joining them (by using column A) to produce the final view. This query can become huge because of the necessary nested joins.

Do you know if someone could do this simpler with oracle ?

Thank you.

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Fri Oct 13 2000 - 11:04:31 CDT