SQL GROUP BY: 例

例
サンプルデータベース

#1

SELECT department_id FROM employees GROUP BY department_id;

#2

SELECT DISTINCT department_id FROM employees ORDER BY department_id;

#3

SELECT department_id, COUNT(employee_id) headcount FROM employees GROUP BY department_id;

#4

SELECT department_name, COUNT(employee_id) headcount FROM employees e INNER JOIN departments d ON d.department_id = e.department_id GROUP BY department_name;

#5

SELECT department_name, COUNT(employee_id) headcount FROM employees e INNER JOIN departments d ON d.department_id = e.department_id GROUP BY department_name ORDER BY headcount DESC;

#6

SELECT department_name, COUNT(employee_id) headcount FROM employees e INNER JOIN departments d ON d.department_id = e.department_id GROUP BY department_name HAVING headcount > 5 ORDER BY headcount DESC;

#7

SELECT department_name, MIN(salary) min_salary, MAX(salary) max_salary, ROUND(AVG(salary), 2) average_salary FROM employees e INNER JOIN departments d ON d.department_id = e.department_id GROUP BY department_name;

#8

SELECT department_name, SUM(salary) total_salary FROM employees e INNER JOIN departments d ON d.department_id = e.department_id GROUP BY department_name;

#9

SELECT department_name, job_title, COUNT(employee_id) FROM employees e INNER JOIN departments d ON d.department_id = e.department_id INNER JOIN jobs j ON j.job_id = e.job_id GROUP BY department_name , job_title;

テーブルリスト