kurs:rollup_und_cube
Differences
This shows you the differences between two versions of the page.
| Both sides previous revision Previous revision Next revision | Previous revision | ||
|
kurs:rollup_und_cube [2010/05/18 11:15] 127.0.0.1 external edit |
kurs:rollup_und_cube [2014/09/10 21:22] (current) |
||
|---|---|---|---|
| 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> | <code> | ||
| SELECT nvl(departments.department_name, 'Summe Department') as dep, | SELECT nvl(departments.department_name, 'Summe Department') as dep, | ||
kurs/rollup_und_cube.1274174146.txt.gz ยท 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
