Particular GROUP BY sum requiring analytic function(s) -I guess...-

Hi,
Let's say I have the following table B containing

SQL> select * from b order by bl_id;

NAME                      BYTES     BLOCKS     FILEID      BL_ID


-------------------- ---------- ---------- ---------- ----------

SEGMENT_NAME           8388608        512        100          1
SEGMENT_NAME           8388608        512        100          2
SEGMENT_NAME           8388608        512        100          3
SEGMENT_NAME           8388608        512        100          4
SEGMENT_NAME           8388608        512        100          5
SEGMENT_NAME           8388608        512        100          6
SEGMENT_NAME           8388608        512        100          7
SEGMENT_NAME           8388608        512        100          8
SEGMENT_NAME           8388608        512        100          9
SEGMENT_NAME           8388608        512        100         10
SEGMENT_NAME           8388608        512        100         11
SEGMENT_NAME           8388608        512        100         12
SEGMENT_NAME           8388608        512        100         13
SEGMENT_NAME           8388608        512        100         14
SEGMENT_NAME           8388608        512        100         15
SEGMENT_NAME           8388608        512        100         16
SEGMENT_NAME           8388608        512        100         17
SEGMENT_NAME           8388608        512        100         18
SEGMENT_NAME          67108864       4096        100         19
SEGMENT_NAME          67108864       4096        100         20
SEGMENT_NAME          67108864       4096        100         21
SEGMENT_NAME          67108864       4096        100         22
free                  63963136       3904        100         23
free                 119537664       7296        100         24
SEGMENT_NAME           1048576         64        100         25
SEGMENT_NAME           1048576         64        100         26
SEGMENT_NAME           1048576         64        100         27
free                   3145728        192        100         28

28 rows selected.

I'd like to get the following output:
SQL> select name, sum(bytes) from b

   2 group by ??? ;

NAME                  SUM(BYTES)


-------------------- ----------

SEGMENT_NAME         419430400
free                 183500800
SEGMENT_NAME           3145728
free                   3145728

As you can see I've grouped by my figures by NAME *but* I've kept the order of the BL_ID figures: as long as I find the same NAME I sum up BYTES, then I encounter another value for NAME: 'free', so I start summing up BYTES values again for this new value of NAME as long as it remains 'free', then again 3 times 'SEGMENT_NAME' => I display the sum of BYTES for the 3 adjacent rows, and finally the ultimate sole value for 'free', 3145728.
Thus the ORDER BY BL_ID is *extremely* important (to get contiguous pieces of occupied/free space).

I couldn't manage to find the SQL that'll give me this output, I guess analytic functions should allow for an easy and elegant way of getting it: who could please help me ?

Thanks a lot in advance.
SR Received on Mon Apr 30 2007 - 08:26:52 CDT