kurs:rollup_und_cube
Differences
This shows you the differences between two versions of the page.
|
kurs:rollup_und_cube [2010/10/30 13:11] mh |
kurs:rollup_und_cube [2014/09/10 21:22] |
||
|---|---|---|---|
| Line 1: | Line 1: | ||
| - | <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> | ||
kurs/rollup_und_cube.txt ยท Last modified: 2014/09/10 21:22 (external edit)
Page Tools
Except where otherwise noted, content on this wiki is licensed under the following license: CC Attribution-Share Alike 4.0 International
