This shows you the differences between two versions of the page.
Both sides previous revision Previous revision Next revision | Previous revision | ||
kurs:group_by_beispiele [2012/09/25 17:22] 127.0.0.1 external edit |
kurs:group_by_beispiele [2020/05/18 11:25] (current) admin |
||
---|---|---|---|
Line 1: | Line 1: | ||
+ | <code> | ||
+ | select | ||
+ | department_name, | ||
+ | count(*) c | ||
+ | from employees e join departments d | ||
+ | on e.department_id = d.department_id | ||
+ | group by | ||
+ | department_name | ||
+ | having count(*) >= 5 | ||
+ | order by c desc | ||
+ | </code> | ||
+ | |||
+ | |||
+ | <code> | ||
+ | with ym as ( | ||
+ | select employee_id, | ||
+ | -- extract(year from hire_date) y, | ||
+ | extract(month from hire_date) m | ||
+ | from employees | ||
+ | ) | ||
+ | select m, count(employee_id) | ||
+ | from ym | ||
+ | group by m | ||
+ | order by m | ||
+ | </code> | ||
+ | |||
+ | |||
+ | |||
+ | <code> | ||
+ | with gehaltklassen as ( | ||
+ | select salary, case when salary < 7000 then 0 | ||
+ | when salary < 12000 then 1 | ||
+ | else 2 end gehaltklasse | ||
+ | from employees | ||
+ | ) | ||
+ | select gehaltklasse, dg, count(dg) from ( | ||
+ | select gehaltklasse, decode(gehaltklasse, | ||
+ | 0, 'wenig', | ||
+ | 1, 'mittel', | ||
+ | 2, 'viel') dg | ||
+ | from gehaltklassen ) | ||
+ | group by gehaltklasse, dg | ||
+ | order by gehaltklasse | ||
+ | </code> | ||
<code> | <code> |