Three good solutions, thank you all.
Monty
JusungYang_at_yahoo.com (Jusung Yang) wrote in message news:<130ba93a.0303211054.33407b25_at_posting.google.com>...
> Analytic functions with case should do it:
>
> SQL> select a,b,c,
> 2 ( case c
> 3 when 0 then null
> 4 else (min(b) over (partition by a))
> 5 end) min_b,
> 6 sum(b) over (partition by a order by b) tot_b
> 7 from test;
>
> A B C MIN_B TOT_B
> ---------- ---------- ---------- ---------- ----------
> 1 1 1 1 1
> 1 3 1 1 4
> 2 1 0 1
> 3 6 1 6 6
> 3 8 0 14
> 3 9 0 23
> 4 16 0 16
> 4 21 1 16 37
> 4 61 1 16 98
>
> 9 rows selected.
>
> SQL>
>
>
> - Jusung Yang
>
>
> mmontreaux_at_hotmail.com (Monty) wrote in message news:<6284dd3.0303210653.6f64bc6_at_posting.google.com>...
> > Hi everyone, I have a requirement that I think will require the use of
> > Oracle analytic functions. Unfortunately I'm a bit dumbfounded by
> > this. If someone could suggest a solution to my dilemma or point me in
> > the right direction I would be most grateful.
> > Thank you
> > Monty
> >
> >
> > CREATE TABLE test(a NUMBER, b NUMBER, c NUMBER);
> > INSERT INTO test(a,b,c) VALUES (1,1,1);
> > INSERT INTO test(a,b,c) VALUES (1,3,1);
> > INSERT INTO test(a,b,c) VALUES (2,1,0);
> > INSERT INTO test(a,b,c) VALUES (3,6,1);
> > INSERT INTO test(a,b,c) VALUES (3,8,0);
> > INSERT INTO test(a,b,c) VALUES (3,9,0);
> > INSERT INTO test(a,b,c) VALUES (4,16,0);
> > INSERT INTO test(a,b,c) VALUES (4,21,1);
> > INSERT INTO test(a,b,c) VALUES (4,61,1);
> >
> > SELECT a,b,c,SomethingElse1,SomethingElse2 FROM test ORDER BY a ASC, b
> > ASC;
> >
> > What I would like is for SomethingElse1 to be
> > a) Whenever c is 0, SomethingElse1 is null.
> > b) Whenever c is 1, SomethingElse1 is the minimum value for b
> > for all other rows that have the same a value.
> >
> > What I would like is for SomethingElse2 to be
> > c) SomethingElse2 is the commulative total of b's for all the
> > rows that have the same a value when ordered a ASC, b ASC as
> > above.
> >
> > ie, the output of the query would be:
> >
> > SQL>
> > SQL>
> > SQL>
> > SQL>
> > SQL> SELECT a,b,c,SomethingElse1,SomethingElse2 FROM test ORDER BY a
> > ASC,b ASC;
> >
> > A B C SomethingElse1 SomethingElse2
> > ---------- ---------- ---------- ------------------ -----------------
> > 1 1 1 1 1
> > 1 3 1 1 4
> > 2 1 0 1
> > 3 6 1 6 6
> > 3 8 0 14
> > 3 9 0 23
> > 4 16 0 16
> > 4 21 1 16 37
> > 4 61 1 16 98
> >
> > 9 rows selected.
> >
> > SQL>
> >
> > We are currently using Oracle 8.1.7 under Windows.
Received on Sat Mar 22 2003 - 10:07:50 CST
