Count employees, group by CUBE(department no, job title)

 
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.

Related examples in the same category

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

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

Related examples in the same category