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

On 30 Apr 2007 06:26:52 -0700, Sébastien de Mapias <sglrigaud_at_gmail.com> wrote:

>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

As the 'table' obviously is a copy of dba_extents, and dba_segments and dba_free_space fulfill your requirement quite nicely, what pressing business need has made you set this up?

-- 
Sybrand Bakker
Senior Oracle DBA