/* die zahl der employees pro department die MEHR als 6000 verdienen
   und die Gehaltssumme ALLER Employees
*/

with salary_dep as (
  select d.department_id,
         department_name,
         salary
    from employees e 
    join departments d
      on e.department_id = d.department_id    
),
sum_dep_komplett as (
  select department_id,
         department_name, 
         sum(salary) sum_sal,
         count(*) count_emp
  from salary_dep
  group by department_id, department_name
),
sum_dep_sal_gt_6000 as (
  select department_id,
         department_name, 
         sum(salary) sum_sal,
         count(*) count_emp
  from salary_dep
  where salary > 6000
  group by department_id, department_name
)
select sdk.department_name, 
       sdk.sum_sal summe_komplett,
       sd6000.sum_sal summe_mehr_als_6000,
       sdk.count_emp,
       sd6000.count_emp
  from 
  sum_dep_komplett sdk join sum_dep_sal_gt_6000 sd6000
on sdk.department_id = sd6000.department_id