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/10/30 13:04]
mh
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>​ <​code>​
   SELECT grouping(DEPARTMENT_NAME),​   SELECT grouping(DEPARTMENT_NAME),​
Line 12: Line 23:
 </​code>​ </​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>​
kurs/rollup_und_cube.txt ยท Last modified: 2014/09/10 21:22 (external edit)