CODEWITHSUNDEEP
oracle-databaserollup
Sigularity
Sigularity

Reputation: 967

Rollup function and percentage value in total

I tried to display tablespace usage by using a ROLLUP function and the result looks good. However, as you can see, percent_free of a Total is just a sum instead of Percentage. Can you help me to change the below query to get a percent value of a Total?

SELECT nvl(df.tablespace_name,'Total') TABLESPACE,
    sum(df.total_space_mb) TOTAL_SPACE_MB,
    sum((df.total_space_mb - fs.free_space_mb)) USED_SPACE_MB,
    sum(fs.free_space_mb) FREE_SPACE_MB,
    sum(ROUND(100 * (fs.free_space / df.total_space),2)) PERCENT_FREE
FROM (SELECT tablespace_name, SUM(bytes) TOTAL_SPACE,
   ROUND(SUM(bytes) / 1048576) TOTAL_SPACE_MB
   FROM dba_data_files
   GROUP BY tablespace_name) df,
  (SELECT tablespace_name, SUM(bytes) FREE_SPACE,
    ROUND(SUM(bytes) / 1048576) FREE_SPACE_MB
    FROM dba_free_space
    GROUP BY tablespace_name) fs
WHERE df.tablespace_name = fs.tablespace_name(+)
GROUP BY rollup(df.tablespace_name)
ORDER BY df.tablespace_name


TABLESPACE      TOTAL_SPACE_MB USED_SPACE_MB FREE_SPACE_MB PERCENT_FREE
--------------- -------------- ------------- ------------- ------------
DEMO                      1500            47          1453        96.87
NORMAL                      10             1             9        88.75
SYSAUX                    7990          7600           390         4.88
SYSTEM                     970           961             9           .9
UNDOTBS1                  1690            24          1666        98.57
USERS                     5275           964          4311        81.73
Total                    17435          9597          7838        371.7

Upvotes: 0

Views: 1079

Answers (1)

Mighty.Moogle
Mighty.Moogle

Reputation: 115

You can use DECODE for this: 

DECODE(df.tablespace_name, NULL,
        ROUND(100*SUM(fs.FREE_SPACE)/SUM(df.total_space),2),
        SUM(ROUND(100 * (fs.free_space / df.total_space),2))) PERCENT_FREE,

When it is the 'Total' row (tablespace_name is null) use the round on sums over the whole table. Then calculate other rows as usual in the else clause of the DECODE.

SELECT nvl(df.tablespace_name,'Total') TABLESPACE,
    sum(df.total_space_mb) TOTAL_SPACE_MB,
    sum((df.total_space_mb - fs.free_space_mb)) USED_SPACE_MB,
    sum(fs.free_space_mb) FREE_SPACE_MB,
    DECODE(df.tablespace_name, NULL,
      ROUND(100*SUM(fs.FREE_SPACE)/SUM(df.total_space),2),
      SUM(ROUND(100 * (fs.free_space / df.total_space),2))) PERCENT_FREE
FROM (SELECT tablespace_name, SUM(bytes) TOTAL_SPACE,
   ROUND(SUM(bytes) / 1048576) TOTAL_SPACE_MB
   FROM dba_data_files
   GROUP BY tablespace_name) df,
  (SELECT tablespace_name, SUM(bytes) FREE_SPACE,
    ROUND(SUM(bytes) / 1048576) FREE_SPACE_MB
    FROM dba_free_space
    GROUP BY tablespace_name) fs
WHERE df.tablespace_name = fs.tablespace_name(+)
GROUP BY rollup(df.tablespace_name)
ORDER BY df.tablespace_name

Upvotes: 1

Related Questions