Count employees, group by CUBE(department no, job title) : CUBE « Analytical Functions « Oracle PL/SQL Tutorial

SQL> create table emp
  2  ( empno      NUMBER(4)    constraint E_PK primary key
  3  , ename      VARCHAR2(8)
  4  , init       VARCHAR2(5)
  5  , job        VARCHAR2(8)
  6  , mgr        NUMBER(4)
  7  , bdate      DATE
  8  , sal        NUMBER(6,2)
  9  , comm       NUMBER(6,2)
 10  , deptno     NUMBER(2)    default 10
 11  ) ;

Table created.

SQL> insert into emp values(1,'Tom','N',   'TRAINER', 13,date '1965-12-17',  800 , NULL,  20);

1 row created.

SQL> insert into emp values(2,'Jack','JAM', 'Tester',6,date '1961-02-20',  1600, 300,   30);

1 row created.

SQL> insert into emp values(3,'Wil','TF' ,  'Tester',6,date '1962-02-22',  1250, 500,   30);

1 row created.

SQL> insert into emp values(4,'Jane','JM',  'Designer', 9,date '1967-04-02',  2975, NULL,  20);

1 row created.

SQL> insert into emp values(5,'Mary','P',  'Tester',6,date '1956-09-28',  1250, 1400,  30);

1 row created.

SQL> insert into emp values(6,'Black','R',   'Designer', 9,date '1963-11-01',  2850, NULL,  30);

1 row created.

SQL> insert into emp values(7,'Chris','AB',  'Designer', 9,date '1965-06-09',  2450, NULL,  10);

1 row created.

SQL> insert into emp values(8,'Smart','SCJ', 'TRAINER', 4,date '1959-11-26',  3000, NULL,  20);

1 row created.

SQL> insert into emp values(9,'Peter','CC',   'Designer',NULL,date '1952-11-17',  5000, NULL,  10);

1 row created.

SQL> insert into emp values(10,'Take','JJ', 'Tester',6,date '1968-09-28',  1500, 0,     30);

1 row created.

SQL> insert into emp values(11,'Ana','AA',  'TRAINER', 8,date '1966-12-30',  1100, NULL,  20);

1 row created.

SQL> insert into emp values(12,'Jane','R',   'Manager',   6,date '1969-12-03',  800 , NULL,  30);

1 row created.

SQL> insert into emp values(13,'Fake','MG',   'TRAINER', 4,date '1959-02-13',  3000, NULL,  20);

1 row created.

SQL> insert into emp values(14,'Mike','TJA','Manager',   7,date '1962-01-23',  1300, NULL,  10);

1 row created.

SQL>
SQL> select   deptno, job
  2  ,        count(empno) headcount
  3  from     emp
  4  group by CUBE(deptno, job);

    DEPTNO JOB       HEADCOUNT
---------- -------- ----------
                            14
           Tester            4
           Manager           2
           TRAINER           4
           Designer          4
        10                   3
        10 Manager           1
        10 Designer          2
        20                   5
        20 TRAINER           4
        20 Designer          1
        30                   6
        30 Tester            4
        30 Manager           1
        30 Designer          1

15 rows selected.

SQL>
SQL>
SQL> drop table emp;

Table dropped.

16.8.CUBE
	16.8.1.	Using the CUBE Clause
	16.8.2.	Include two columns in CUBE function
	16.8.3.	GROUPING() with CUBE demo
	16.8.4.	Using ROLLUP, CUBE, and GROUPING SETS Operators with Analytic Functions
	16.8.5.	Use CUBE and RANK() to get all rankings of salaries by city and description
	16.8.6.	CUBE: get rollups for both columns
	16.8.7.	Count employees, group by CUBE(department no, job title)
	16.8.8.	group by CUBE(department no, job title)