This shows you the differences between two versions of the page.
Next revision | Previous revision | ||
kurs:rollup_und_cube [2008/12/10 11:30] mh created |
kurs:rollup_und_cube [2011/05/31 12:57] mh |
||
---|---|---|---|
Line 1: | Line 1: | ||
+ | <code> | ||
+ | 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) | ||
+ | </code> | ||
+ | |||
+ | <code> | ||
+ | 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, ) | ||
+ | </code> | ||
+ | |||
+ | <code> | ||
+ | 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, ) | ||
+ | </code> | ||
+ | |||
+ | <code> | ||
+ | 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 | ||
+ | </code> |