Count employees, group by ROLLUP(department no, job title) : ROLLUP « 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 ROLLUP(deptno, job);

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

11 rows selected.

SQL>
SQL>
SQL> drop table emp;

Table dropped.

SQL>








16.24.ROLLUP
16.24.1.Using the ROLLUP Clause
16.24.2.Passing Multiple Columns to ROLLUP
16.24.3.Changing the Position of Columns Passed to ROLLUP
16.24.4.Uses AVG() with ROLLUP
16.24.5.Using a Column Multiple Times in a GROUP BY Clause
16.24.6.The ROLLUP function was provided to conveniently give the sum on the aggregate.
16.24.7.A two-dimensional grouping with ROLLUP
16.24.8.A SUM using ROLLUP.
16.24.9.Rollup with two columns
16.24.10.Case with grouping and rollup
16.24.11.Count employees, group by ROLLUP(department no, job title)
16.24.12.group by ROLLUP(department no, job title)