[Oracle] 그룹 함수[COUNT, MAX, MIN, AVG, SUM, GROUP]

--COUNT
--부서명이 NULL이 아닌,
--레코드의 수
SELECT COUNT(department_id)
  FROM employees
  WHERE department_id IS NOT NULL; 

--MAX, MIN, AVG, SUM

--부서명이 NULL이 아닌,
--급여액이 가장 높은 사람, 가장 낮은 사람, 평균 소숫점 1자리, 합
SELECT MAX(salary), MIN(salary), ROUND(AVG(salary), 1), SUM(salary)
  FROM employees
  WHERE department_id IS NOT NULL;

 

--GROUP BY

--부서명이 NULL이 아닌,
--부서별 직원수
SELECT COUNT(employee_id)
  FROM employees
  WHERE department_id IS NOT NULL
  GROUP BY department_id;

--GROUP BY, JOIN

--부서명이 NULL이 아닌,
--부서별 부서명, 인원수, 평균급여액, 최고급여액, 최저급여액, 합계
SELECT departments.department_name "부서명", COUNT(employees.employee_id) "인원수",
        ROUND(AVG(employees.salary), 1) "평균급여액", MAX(employees.salary) "최고급여액",
        MIN(employees.salary) "최저급여액", SUM(employees.salary) "급여액 합계"
  FROM employees, departments
  WHERE employees.department_id = departments.department_id
    AND employees.department_id IS NOT NULL
  GROUP BY departments.department_name;

 

--GROUP BY, JOIN, HAVING

--부서명이 NULL이 아닌,
--인원수가 5명이상
SELECT departments.department_name "부서명", COUNT(employees.employee_id) "인원수",
        ROUND(AVG(employees.salary), 1) "평균급여액", MAX(employees.salary) "최고급여액",
        MIN(employees.salary) "최저급여액", SUM(employees.salary) "급여액 합계"
  FROM employees, departments
  WHERE employees.department_id = departments.department_id
    AND employees.department_id IS NOT NULL
  GROUP BY departments.department_name
  HAVING COUNT(employees.employee_id) >= 5;