Differences

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

@@ Line 1: / Line 1: @@
+<code>
+/* 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
+</code>

dokuwiki