Getting a Total of Values IN() 1

[tekdudedude]

Hello,

I am stuck. I have the following query that will produce a list of each Oracle tablespace and its total bytes for the schema entered:

set verify off
set linesize 140

ACCEPT SCHEMA PROMPT 'Enter schema name: '

SELECT tablespace_name, sum(bytes) Bytes
FROM dba_segments
WHERE tablespace_name IN('SI_DATA_&SCHEMA._TS','SI_INDEX_&SCHEMA._TS','TIDATA_&SCHEMA._TS','TIINDX_&SCHEMA._TS','TITEXTINDX_&SCHEMA._TS')
GROUP BY (tablespace_name);

The output looks like this:

TABLESPACE_NAME           BYTES
------------------------- ----------
SI_INDEX_SCOTT_TS            11993088
SI_DATA_SCOTT_TS              8978432
TIDATA_SCOTT_TS               2031616
TITEXTINDX_SCOTT_TS        1048838144
TIINDX_SCOTT_TS               1638400

How can I also get the total value (in bytes) of each entry of my IN clause?

The output I am looking for would be:

TABLESPACE_NAME           BYTES
------------------------- ----------
SI_INDEX_SCOTT_TS            11993088
SI_DATA_SCOTT_TS              8978432
TIDATA_SCOTT_TS               2031616
TITEXTINDX_SCOTT_TS        1048838144
TIINDX_SCOTT_TS               1638400

[b]TOTAL                      1073479680[/b]

Thanks,

TD

 

[jaxtell]

there is a better way to do it, but this should work

SELECT tablespace_name, sum(bytes) Bytes
FROM dba_segments
WHERE tablespace_name IN('SI_DATA_&SCHEMA._TS','SI_INDEX_&SCHEMA._TS','TIDATA_&SCHEMA._TS','TIINDX_&SCHEMA._TS','TITEXTINDX_&SCHEMA._TS')
GROUP BY (tablespace_name)
union all
select 'TOTAL', sum(bytes1) 
from (SELECT sum(bytes) bytes1
FROM dba_segments
WHERE tablespace_name IN('SI_DATA_&SCHEMA._TS','SI_INDEX_&SCHEMA._TS','TIDATA_&SCHEMA._TS','TIINDX_&SCHEMA._TS','TITEXTINDX_&SCHEMA._TS'));

that works for me

 

[tekdudedude]

jaxtell,

YES! That was it. I always learn so much from you guys.

Thanks for posting,

TD