CUBE clause: return rows containing a subtotal for all combinations of columns included in the CUBE clause along with a total : Cube « Analytical Functions « Oracle PL / SQL






CUBE clause: return rows containing a subtotal for all combinations of columns included in the CUBE clause along with a total

 


SQL> -- create demo table
SQL> create table Employee(
  2    ID                 VARCHAR2(4 BYTE)         NOT NULL,
  3    First_Name         VARCHAR2(10 BYTE),
  4    Last_Name          VARCHAR2(10 BYTE),
  5    Start_Date         DATE,
  6    End_Date           DATE,
  7    Salary             Number(8,2),
  8    City               VARCHAR2(10 BYTE),
  9    Description        VARCHAR2(15 BYTE)
 10  )
 11  /

Table created.

SQL>
SQL> -- prepare data
SQL> insert into Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary,  City,       Description)
  2               values ('01','Jason',    'Martin',  to_date('19960725','YYYYMMDD'), to_date('20060725','YYYYMMDD'), 1234.56, 'Toronto',  'Programmer')
  3  /

1 row created.

SQL> insert into Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary,  City,       Description)
  2                values('02','Alison',   'Mathews', to_date('19760321','YYYYMMDD'), to_date('19860221','YYYYMMDD'), 6661.78, 'Vancouver','Tester')
  3  /

1 row created.

SQL> insert into Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary,  City,       Description)
  2                values('03','James',    'Smith',   to_date('19781212','YYYYMMDD'), to_date('19900315','YYYYMMDD'), 6544.78, 'Vancouver','Tester')
  3  /

1 row created.

SQL> insert into Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary,  City,       Description)
  2                values('04','Celia',    'Rice',    to_date('19821024','YYYYMMDD'), to_date('19990421','YYYYMMDD'), 2344.78, 'Vancouver','Manager')
  3  /

1 row created.

SQL> insert into Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary,  City,       Description)
  2                values('05','Robert',   'Black',   to_date('19840115','YYYYMMDD'), to_date('19980808','YYYYMMDD'), 2334.78, 'Vancouver','Tester')
  3  /

1 row created.

SQL> insert into Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary, City,        Description)
  2                values('06','Linda',    'Green',   to_date('19870730','YYYYMMDD'), to_date('19960104','YYYYMMDD'), 4322.78,'New York',  'Tester')
  3  /

1 row created.

SQL> insert into Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary, City,        Description)
  2                values('07','David',    'Larry',   to_date('19901231','YYYYMMDD'), to_date('19980212','YYYYMMDD'), 7897.78,'New York',  'Manager')
  3  /

1 row created.

SQL> insert into Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary, City,        Description)
  2                values('08','James',    'Cat',     to_date('19960917','YYYYMMDD'), to_date('20020415','YYYYMMDD'), 1232.78,'Vancouver', 'Tester')
  3  /

1 row created.

SQL>
SQL>
SQL>
SQL> -- display data in the table
SQL> select * from Employee
  2  /

ID   FIRST_NAME LAST_NAME  START_DATE  END_DATE        SALARY CITY       DESCRIPTION
---- ---------- ---------- ----------- ----------- ---------- ---------- ---------------
01   Jason      Martin     1996-JUL-25 2006-JUL-25    1234.56 Toronto    Programmer
02   Alison     Mathews    1976-MAR-21 1986-FEB-21    6661.78 Vancouver  Tester
03   James      Smith      1978-DEC-12 1990-MAR-15    6544.78 Vancouver  Tester
04   Celia      Rice       1982-OCT-24 1999-APR-21    2344.78 Vancouver  Manager
05   Robert     Black      1984-JAN-15 1998-AUG-08    2334.78 Vancouver  Tester
06   Linda      Green      1987-JUL-30 1996-JAN-04    4322.78 New York   Tester
07   David      Larry      1990-DEC-31 1998-FEB-12    7897.78 New York   Manager
08   James      Cat        1996-SEP-17 2002-APR-15    1232.78 Vancouver  Tester

8 rows selected.

SQL>
SQL>
SQL> --CUBE clause: return rows containing a subtotal for all combinations of columns included in the CUBE clause along with a total.
SQL>
SQL> SELECT city, description, SUM(salary)
  2  FROM employee
  3  GROUP BY CUBE(city, description);

CITY       DESCRIPTION     SUM(SALARY)
---------- --------------- -----------
                              32574.02
           Tester              21096.9
           Manager            10242.56
           Programmer          1234.56
Toronto                        1234.56
Toronto    Programmer          1234.56
New York                      12220.56
New York   Tester              4322.78
New York   Manager             7897.78
Vancouver                      19118.9
Vancouver  Tester             16774.12
Vancouver  Manager             2344.78

12 rows selected.

SQL>
SQL>
SQL>
SQL> -- clean the table
SQL> drop table Employee
  2  /

Table dropped.

SQL>
SQL>
           
         
  








Related examples in the same category

1.Cube implies reducing tables by rolling up different columns (dimensions)
2.Grouping with Cube
3.The sequence of the columns passed into Cube function
4.Use CUBE and RANK() to get all rankings of sales by product type ID and employee ID
5.Demonstrate Partial CUBE operation
6.use GROUPING SETS to produce just a subset of CUBE
7.Count employees, group by CUBE(department no, job title)
8.group by CUBE(department no, job title)
9.group by cube