column "#B" format 999999999999999990
column "Type" format a20
column "User" format a16
column "Tier" format a30
column "#F" format 99999990
column "#C" format 99999999990
set linesize 132
set pagesize 49999
set trim on
set trimspool on
spool bytype_ontape.out
select sum(file_size_in_bytes) "#B",
       count(*) "#F",
       count(EVENT_COUNT) "#C",
       decode(grouping(to_char(df.create_date,'YYYY-MM')),1,'total',to_char(df.create_date,'YYYY-MM')) made,
       decode(grouping(file_type_desc),1,'total',file_type_desc) "Type",
       decode(grouping(df.create_user),1,'total',df.create_user) "User",
       decode(grouping(data_tier),1,'total',data_tier) "Tier"
from
  data_files df
 join file_types ft using (file_type_id)
 join data_tiers dt using (data_tier_id)
 join data_file_locations dfl using (file_id)  
where df.create_date > '01-jan-2002'
  and dfl.volume_id is not null
group by cube(to_char(df.create_date,'YYYY-MM'),file_type_desc,df.create_user,data_tier)
order by made,file_type_desc,df.create_user,data_tier
/
spool close
quit
