you can use decode expressions. for example,
SELECT A
, SUM(DECODE(B,b1,DECODE(C,c2,1,0),0)) AS count1
, SUM(DECODE(SIGN(B-b3),-1,1,DECODE(C,c2,1,0))) AS count2
FROM basic_table
GROUP BY A
<dili66_at_my-deja.com> wrote in message news:8s7bq8$q10$1_at_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.
>
> D.
>
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
>
Received on Sun Oct 15 2000 - 21:52:24 CDT
