Cross-Tabulation

Crosstab report

Using GROUP BY CUBE

Number of employees, average yearly salary across all combinations of department and job category:

SELECT DECODE(GROUPING(department_name), 1, 'All Departments', department_name) AS department_name,
       DECODE(GROUPING(job_id), 1, 'All Jobs', job_id) AS job_id,
       COUNT(*) "Total Empl", 
       AVG(salary) * 12 "Average Sal"
  FROM employees e, departments d
 WHERE d.department_id = e.department_id
 GROUP BY CUBE (department_name, job_id);

DEPARTMENT_NAME                JOB_ID     Total Empl Average Sal
------------------------------ ---------- ---------- -----------
Accounting                     AC_ACCOUNT          1       99600
Accounting                     AC_MGR              1      144000
Accounting                     All Jobs            2      121800
Administration                 AD_ASST             1       52800

Notes

1. GROUPING identifies super-aggregates
2. DECODE provides text identifier