column "#B" format 999999999999999990
column "#V" format 999999999999990
column "#F" format 99999990
column data_type format a30
column library format a18
set linesize 132
set pagesize 49999
set trim on
set trimspool on
spool kutschke4.out
select sum(file_size) "#B",
       count(distinct volume) "#V",
       count(*) "#F",
       decode(grouping(to_char(pnfs_date,'YYYY-MM')),1,'total',to_char(pnfs_date,'YYYY-MM')) made,
       decode(grouping(library),1,'total',library) "Library",
       decode(grouping(fdt.description),1,'total',fdt.description) data_type
from
  herber.en_volumes env,
  herber.en_files enf,
  herber.en_datasets eds,
  filecatalog.cdf2_dataset_registries fdr,
  filecatalog.cdf2_data_types fdt
where env.volume_id = enf.volume_id
  and enf.dataset_id = eds.dataset_id
  and eds.dataset = fdr.dataset_id
  and fdr.data_type_id = fdt.data_type_id
group by cube(to_char(pnfs_date,'YYYY-MM'),library,fdt.description)
order by made,library,data_type
/
spool close

