help with query (summarization)

From: Syltrem <syltremspammenot_at_videotron.com>
Date: Tue, 4 Jun 2002 13:41:24 -0400
Message-ID: <EY6L8.2378$H67.13089@tor-nn1.netcom.ca>

I`m having trouble finding the best way (or any way at all) to get a sum by tablespace, for the column "bytes" in this query. If possible, have the same query print the details (that`s what it does now), then the summary.
Oracle forces me to add the bytes column to my group by, therefore I do not get a sum... I don't think it`s worth posting what I`ve tried so far. Can someone help me?
Thanks!

select substr(owner,1,6) "Owner", SUBSTR(tablespace_name,1,17) "Tablespace",

        substr(segment_type,1,2) "Ty", substr(segment_name,1,31) "Segment",
        decode(floor(bytes/1024/1024/5), 0, bytes/1024 || 'K',
                ltrim(TO_CHAR(bytes/1024/1024,'9999.9')) || 'M') "Size",
        extents "# Ext", next_extent/1024 "ExtSzK"

from dba_segments
where segment_name like upper('&segment_name%') escape '\'   and tablespace_name like upper('&tablespace_name%') group by owner, segment_name, segment_type, tablespace_name, bytes,

        extents, next_extent
having extents >= 0&min_nb_of_extents

   and bytes >= 0&min_size_mb*1024*1024 order by owner, segment_name, segment_type desc;

--

Syltrem
http://pages.infinit.net/syltrem (OpenVMS related web site - en français)
To reply to myself directly, remove .spammenot from my address

Received on Tue Jun 04 2002 - 12:41:24 CDT