User Tools

Site Tools


kurs:rollup_und_cube

Differences

This shows you the differences between two versions of the page.

Link to this comparison view

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)