dokuwiki

select department_name, jj, sum(salary) from (
select department_name,        
       regexp_replace(job_id, '^[[:alpha:]]{2}_', '') jj,
       salary from employees e, departments d
where e.department_id = d.department_id
)
group by cube (department_name, jj)

  SELECT grouping(DEPARTMENT_NAME),
                  DEPARTMENT_NAME,
         grouping(COUNTRY_NAME), 
                  COUNTRY_NAME, 
                  (count(EMPLOYEES.EMPLOYEE_ID)) c
      FROM DEPARTMENTS , LOCATIONS , COUNTRIES , EMPLOYEES 
  WHERE ( (LOCATIONS.LOCATION_ID = DEPARTMENTS.LOCATION_ID) AND
          (COUNTRIES.COUNTRY_ID = LOCATIONS.COUNTRY_ID) AND
          (DEPARTMENTS.DEPARTMENT_ID = EMPLOYEES.DEPARTMENT_ID) ) 
   group by rollup (COUNTRIES.COUNTRY_NAME, DEPARTMENTS.DEPARTMENT_NAME,   )

  SELECT grouping(DEPARTMENT_NAME),
                  DEPARTMENT_NAME,
         grouping(COUNTRY_NAME), 
                  COUNTRY_NAME, 
                  (count(EMPLOYEES.EMPLOYEE_ID)) c
      FROM DEPARTMENTS , LOCATIONS , COUNTRIES , EMPLOYEES 
  WHERE ( (LOCATIONS.LOCATION_ID = DEPARTMENTS.LOCATION_ID) AND
          (COUNTRIES.COUNTRY_ID = LOCATIONS.COUNTRY_ID) AND
          (DEPARTMENTS.DEPARTMENT_ID = EMPLOYEES.DEPARTMENT_ID) ) 
   group by cube (COUNTRIES.COUNTRY_NAME, DEPARTMENTS.DEPARTMENT_NAME  )
minus
  SELECT grouping(DEPARTMENT_NAME),
                  DEPARTMENT_NAME,
         grouping(COUNTRY_NAME), 
                  COUNTRY_NAME, 
                  (count(EMPLOYEES.EMPLOYEE_ID)) c
      FROM DEPARTMENTS , LOCATIONS , COUNTRIES , EMPLOYEES 
  WHERE ( (LOCATIONS.LOCATION_ID = DEPARTMENTS.LOCATION_ID) AND
          (COUNTRIES.COUNTRY_ID = LOCATIONS.COUNTRY_ID) AND
          (DEPARTMENTS.DEPARTMENT_ID = EMPLOYEES.DEPARTMENT_ID) ) 
   group by rollup (COUNTRIES.COUNTRY_NAME, DEPARTMENTS.DEPARTMENT_NAME,   )

SELECT  nvl(departments.department_name, 'Summe Department') as dep, 
        grouping(department_name) as gd,
        nvl(job_id, 'Summe Jobs') as job, grouping(job_id) as gj,
        count(last_name), sum(employees.salary)       
  FROM departments, employees
 WHERE ((departments.department_id = employees.department_id))
 group by rollup (department_name, job_id)
 order by department_name, job_id