Re: Help with Oracle Analytic functions

The first part of your 'requirement' is fairly easy to obtain ... however the second part, the running sum grouped by a, isn't. Here is what I have so far; this returns your SomethingElse1 column correctly, but your SomethingElse2 returns the full sum of b grouped by a:

select t.a, t.b, t.c, case when t.c = 0 then null when t.c=1 then a.b end b, s.b
from test t, (select a, min(b) b from test group by a) a, (select a, sum(b) b from test group by a) s
where a.a = t.a
and s.a = t.a
/

         A B C B B ---------- ---------- ---------- ---------- ----------

         1          1          1          1          4
         1          3          1          1          4
         2          1          0                     1
         3          6          1          6         23
         3          8          0                    23
         3          9          0                    23
         4         16          0                    98
         4         21          1         16         98
         4         61          1         16         98

9 rows selected.

If anyone else can point out what I should be doing for the last column I'd really appreciate it.

David Fitzjarrell

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 Fri Mar 21 2003 - 12:17:20 CST