Home » SQL & PL/SQL » SQL & PL/SQL » Is there a way to do a rollup on only one dimension (11.2.0.4, Windows 2012 R2)
Is there a way to do a rollup on only one dimension [message #666755] Thu, 23 November 2017 23:04 Go to next message
juniordbanewbie
Messages: 176
Registered: April 2014
Senior Member
Dear all,

I want to do a snapshot of file#, name, bytes/1024/1024 from v$datafile;

currently I'm using


COLUMN file_num_name_date FORMAT A100
COLUMN m_bytes FORMAT 9999999.999
SET PAGES 34

SELECT file# ||','|| name || ',' || to_char(SYSTIMESTAMP, 'YYYY-MM-DD HH24:MI:SS TZR') file_num_name_date, round(sum(bytes)/1024/1024, 4) m_bytes FROM v$datafile
GROUP BY ROLLUP(file# ||','|| name || ',' || to_char(SYSTIMESTAMP, 'YYYY-MM-DD HH24:MI:SS TZR'))
ORDER BY file# ||','|| name || ',' || to_char(SYSTIMESTAMP, 'YYYY-MM-DD HH24:MI:SS TZR');

I achieved what I wanted


but I'm looking at having 3 dimensions, namely file#, name, to_char(SYSTIMESTAMP, 'YYYY-MM-DD HH24:MI:SS TZR')

and doing a rollup

https://oracle-base.com/articles/misc/rollup-cube-grouping-functions-and-grouping-sets

SELECT file#, name, to_char(SYSTIMESTAMP, 'YYY-MM-DD HH24:MI:SS TZR'), GROUPING_ID(file#, name, to_char(SYSTIMESTAMP, 'YYY-MM-DD HH24:MI:SS TZR')) AS grouping_id , sum(bytes)/1024/1024 m_bytes FROM v$datafile GROUP BY GROUPING SETS (file#);

ERROR at line 1:
ORA-00979: not a GROUP BY expression


Is there a way to use grouping set instead of concatenation to achieve the rollup on one dimension only?


thanks a lot!
Re: Is there a way to do a rollup on only one dimension [message #666756 is a reply to message #666755] Fri, 24 November 2017 01:26 Go to previous messageGo to next message
Michel Cadot
Messages: 65249
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

http://www.dpriver.com/pp/sqlformat.htm

Re: Is there a way to do a rollup on only one dimension [message #666776 is a reply to message #666755] Fri, 24 November 2017 07:51 Go to previous message
Solomon Yakobson
Messages: 2687
Registered: January 2010
Location: Connecticut, USA
Senior Member
SQL> SELECT  file#,
  2          name,
  3          to_char(SYSTIMESTAMP,'YYYY-MM-DD HH24:MI:SS TZR') TS,
  4          GROUPING_ID(file#,name) AS grouping_id,
  5          sum(bytes)/1024/1024 m_bytes
  6    FROM  v$datafile
  7    GROUP BY ROLLUP(file#,name)
  8    HAVING grouping(file#) = grouping(name)
  9    ORDER BY file#
 10  /

     FILE# NAME                                                     TS                             GROUPING_ID    M_BYTES
---------- -------------------------------------------------------- ------------------------------ ----------- ----------
         8 C:\APP\ORACLE12C\ORADATA\SOL122\PDB1SOL122\SYSTEM01.DBF  2017-11-24 08:49:58 -05:00               0        280
         9 C:\APP\ORACLE12C\ORADATA\SOL122\PDB1SOL122\SYSAUX01.DBF  2017-11-24 08:49:58 -05:00               0        750
        10 C:\APP\ORACLE12C\ORADATA\SOL122\PDB1SOL122\UNDOTBS01.DBF 2017-11-24 08:49:58 -05:00               0        525
        11 C:\APP\ORACLE12C\ORADATA\SOL122\PDB1SOL122\USERS01.DBF   2017-11-24 08:49:58 -05:00               0     268.75
                                                                    2017-11-24 08:49:58 -05:00               3    1823.75

SQL> SELECT  file#,
  2          max(name) name, -- since file# is unique
  3          to_char(SYSTIMESTAMP,'YYYY-MM-DD HH24:MI:SS TZR') TS,
  4          GROUPING_ID(file#) AS grouping_id,
  5          sum(bytes)/1024/1024 m_bytes
  6    FROM  v$datafile
  7    GROUP BY ROLLUP(file#)
  8    ORDER BY file#
  9  /

     FILE# NAME                                                     TS                             GROUPING_ID    M_BYTES
---------- -------------------------------------------------------- ------------------------------ ----------- ----------
         8 C:\APP\ORACLE12C\ORADATA\SOL122\PDB1SOL122\SYSTEM01.DBF  2017-11-24 08:49:58 -05:00               0        280
         9 C:\APP\ORACLE12C\ORADATA\SOL122\PDB1SOL122\SYSAUX01.DBF  2017-11-24 08:49:58 -05:00               0        750
        10 C:\APP\ORACLE12C\ORADATA\SOL122\PDB1SOL122\UNDOTBS01.DBF 2017-11-24 08:49:58 -05:00               0        525
        11 C:\APP\ORACLE12C\ORADATA\SOL122\PDB1SOL122\USERS01.DBF   2017-11-24 08:49:58 -05:00               0     268.75
           C:\APP\ORACLE12C\ORADATA\SOL122\PDB1SOL122\USERS01.DBF   2017-11-24 08:49:58 -05:00               1    1823.75

SQL> SELECT  file#,
  2          name,
  3          to_char(SYSTIMESTAMP,'YYYY-MM-DD HH24:MI:SS TZR') TS,
  4          GROUPING_ID(file#,name) AS grouping_id,
  5          sum(bytes)/1024/1024 m_bytes
  6    FROM  v$datafile
  7    GROUP BY grouping sets((),(file#,name))
  8    ORDER BY file#
  9  /

     FILE# NAME                                                     TS                             GROUPING_ID    M_BYTES
---------- -------------------------------------------------------- ------------------------------ ----------- ----------
         8 C:\APP\ORACLE12C\ORADATA\SOL122\PDB1SOL122\SYSTEM01.DBF  2017-11-24 08:49:59 -05:00               0        280
         9 C:\APP\ORACLE12C\ORADATA\SOL122\PDB1SOL122\SYSAUX01.DBF  2017-11-24 08:49:59 -05:00               0        750
        10 C:\APP\ORACLE12C\ORADATA\SOL122\PDB1SOL122\UNDOTBS01.DBF 2017-11-24 08:49:59 -05:00               0        525
        11 C:\APP\ORACLE12C\ORADATA\SOL122\PDB1SOL122\USERS01.DBF   2017-11-24 08:49:59 -05:00               0     268.75
                                                                    2017-11-24 08:49:59 -05:00               3    1823.75

SQL> 

SY.
Previous Topic: Unable to grant Admin privileges on SQL Developer 12c
Next Topic: Oracle Sub-query and AND clause behaviour
Goto Forum:
  


Current Time: Tue Dec 12 02:58:53 CST 2017

Total time taken to generate the page: 0.01732 seconds