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 hr.employees e, hr.departments d
WHERE d.department_id = e.department_id
GROUP BY CUBE (department_name, job_id)
ORDER BY department_name, job_id| DEPARTMENT_NAME | JOB_ID | Total Empl | Average Sal |
|---|---|---|---|
| Accounting | AC_ACCOUNT | 1 | 99600 |
| Accounting | AC_MGR | 1 | 144096 |
| Accounting | All Jobs | 2 | 121848 |
| Administration | AD_ASST | 1 | 52800 |
| Administration | All Jobs | 1 | 52800 |
| All Departments | AC_ACCOUNT | 1 | 99600 |
| All Departments | AC_MGR | 1 | 144096 |
| All Departments | AD_ASST | 1 | 52800 |
| All Departments | AD_PRES | 1 | 288000 |
| All Departments | AD_VP | 2 | 204000 |
| All Departments | All Jobs | 106 | 77481.0566037735849056603773584905660377 |
| All Departments | FI_ACCOUNT | 5 | 95040 |
| All Departments | FI_MGR | 1 | 144096 |
| All Departments | HR_REP | 1 | 78000 |
| All Departments | IT_PROG | 5 | 69120 |
| All Departments | MK_MAN | 1 | 156000 |
| All Departments | MK_REP | 1 | 72000 |
| All Departments | PR_REP | 1 | 120000 |
| All Departments | PU_CLERK | 5 | 33360 |
| All Departments | PU_MAN | 1 | 132000 |
| All Departments | SA_MAN | 5 | 146400 |
| All Departments | SA_REP | 29 | 100758.620689655172413793103448275862069 |
| All Departments | SH_CLERK | 20 | 38580 |
| All Departments | ST_CLERK | 20 | 33420 |
| All Departments | ST_MAN | 5 | 87360 |
| Executive | AD_PRES | 1 | 288000 |
| Executive | AD_VP | 2 | 204000 |
| Executive | All Jobs | 3 | 231999.9999999999999999999999999999999996 |
| Finance | All Jobs | 6 | 103216 |
| Finance | FI_ACCOUNT | 5 | 95040 |
| Finance | FI_MGR | 1 | 144096 |
| Human Resources | All Jobs | 1 | 78000 |
| Human Resources | HR_REP | 1 | 78000 |
| IT | All Jobs | 5 | 69120 |
| IT | IT_PROG | 5 | 69120 |
| Marketing | All Jobs | 2 | 114000 |
| Marketing | MK_MAN | 1 | 156000 |
| Marketing | MK_REP | 1 | 72000 |
| Public Relations | All Jobs | 1 | 120000 |
| Public Relations | PR_REP | 1 | 120000 |
| Purchasing | All Jobs | 6 | 49800 |
| Purchasing | PU_CLERK | 5 | 33360 |
| Purchasing | PU_MAN | 1 | 132000 |
| Sales | All Jobs | 34 | 107470.5882352941176470588235294117647059 |
| Sales | SA_MAN | 5 | 146400 |
| Sales | SA_REP | 29 | 100758.620689655172413793103448275862069 |
| Shipping | All Jobs | 45 | 41706.6666666666666666666666666666666667 |
| Shipping | SH_CLERK | 20 | 38580 |
| Shipping | ST_CLERK | 20 | 33420 |
| Shipping | ST_MAN | 5 | 87360 |