Question on 'Group by' and 'Sum'
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: Question on 'Group by' and 'Sum'

  1. #1
    Join Date
    Nov 2000
    Location
    London
    Posts
    83

    Question

    The following sql
    select fs.tablespace_name,
    (100*((df.bytes-sum(fs.bytes))/df.bytes)) "% Used"
    from sys.dba_data_files df, sys.dba_free_space fs
    where df.file_id(+) = fs.file_id
    group by fs.tablespace_name, df.bytes;

    gives me the output:
    TABLESPACE_NAME % Used
    ------------------------------ ---------
    BOSTOCK 68.798828
    BOSTOCK 36.044922
    BOSTOCK 95.453333
    RBS 10.6
    RBS 10.466667
    RBS 5.6111111
    SERVER_INFO 2.5
    STAGE 50.25

    but as you can see, it is giving me row per number of datafiles in each tablespace. It needs to be one row per tablespace (i.e. here I want the sum of Bostock and RBS instead of three row each)

    Is it possible to modify the Group by statement so they will add up? Or is there other ways to do that, like using Sum? (which I also have no clues!)

    Thanx in advance
    Fiona

    Share on Google+

  2. #2
    Join Date
    Nov 2000
    Posts
    440

    Smile

    Use this select instead:

    SELECT A.TABLESPACE_NAME TABLESPACE,
    ROUND(A.BYTES/1048576,1) MB_TOTAL,
    ROUND(NVL(B.BYTES/1048576, 0), 1) MB_USILISE,
    ROUND(((NVL(B.BYTES,0) * 100) / A.BYTES), 1) "UTILISATION(%)"
    FROM (select tablespace_name, sum(bytes) bytes
    from dba_data_files
    group by tablespace_name) A,
    (select tablespace_name,
    sum(bytes) bytes
    from dba_extents
    group by tablespace_name) B
    WHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME (+)
    ORDER BY 1;


    Steeve Bisson
    EMail: steeve_2@videotron.ca
    Share on Google+

  3. #3
    Join Date
    Nov 2000
    Location
    London
    Posts
    83
    Thanx for the help: it works fine!

    Fiona
    Share on Google+

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width