다중행 함수(multi row function) 그룹함수

- sum(), max(), min(), count(), avg()

SELECT sum(salary) FROM employees;
SELECT max(salary) FROM employees;
SELECT min(salary) FROM employees;
SELECT avg(salary) FROM employees;

SELECT count(*) FROM employees; --무조건 전체 인원수 구하기
SELECT count(commission_pct) FROM employees; --commission_pct가 있는 사람만 구하기(null값 제외)
SELECT avg(commission_pct) FROM employees; --null값 빼고 계산

SELECT sum(salary), min(salary), max(salary), round(avg(salary)), count(*), count(manager_id), count(DISTINCT department_id)
FROM employees;

 

- group by

그룹함수는 보통 group by랑 묶어서 사용

--부서별 급여의 평균을 구하시오
SELECT department_id 부서아이디, round(avg(salary)) 급여평균
FROM employees
GROUP BY department_id
ORDER BY 부서아이디;
--그룹함수를 제외한 모든 컬럼들이 다 GROUP BY 안에 있어야됨
SELECT department_id 부서아이디,first_name 이름, round(avg(salary)) 급여평균
FROM employees
GROUP BY department_id, first_name
ORDER BY 부서아이디;
--이름은 다 개별이니 이건 별 의미가 없음
--부서별,매니저별 평균 급여 구하기
SELECT department_id 부서아이디,manager_id 매니저아이디, avg(salary) 급여평균
FROM employees
GROUP BY department_id, manager_id
ORDER BY 부서아이디;
--group by는 큰 그룹 먼저 써야됨

 

Q) 연봉이 5천만원 이하인 직원에 대하여 부서별 최소급여를 구하시오

SELECT department_id 부서, min(salary) 최소급여
FROM employees
WHERE salary*12<=50000000
GROUP BY department_id
ORDER BY 부서;

Q) job_id의 첫 두글자가 'IT'가 아닌 직원에 대하여 부서별 최소급여를 구하시오

SELECT department_id 부서, min(salary) 최소급여
FROM employees
WHERE substr(job_id,1,2)!='IT'
GROUP BY department_id
ORDER BY 부서;
--WHERE job_id NOT LIKE 'IT%' 이렇게 해도됨

 

Q) 부서아이디가 30이상인 직원에 대해 부서별 최소급여 구하기 (추가조건) 부서별 평균급여가 10000이하인 경우만 구한다

SELECT department_id 부서, min(salary) 최소급여, avg(salary) 평균급여
FROM employees
WHERE department_id>=30
GROUP BY department_id
HAVING avg(salary)<=10000
ORDER BY 부서;
--조건은 일반조건, 그룹조건이 있음
--일반조건: WHERE절
--그룹조건: HAVING절

Q) 부서가 30,80번인 직원들 중에서 급여를 5000이상 10000이하를 받는 직원들에 한해 부서별 평균 급여를 출력하시오.  단 평균 급여가 5500이상인 부서만 출력되어야 하며, 평균 급여가 낮은 부서가 먼저 출력한다

SELECT department_id 부서, avg(salary) 평균급여
FROM employees
WHERE department_id IN (30, 80) AND salary BETWEEN 5000 AND 10000
GROUP BY department_id
HAVING avg(salary)>=5500
ORDER BY 평균급여;

Q) 각 부서별 최대급여와 최소급여 및 평균급여, 합계급여, 인원수를 조회, 출력하시오(직원수가 1명인 경우 의미없으므로 제외)

SELECT department_id 부서,max(salary) 최대급여,min(salary) 최소급여, avg(salary) 평균급여, sum(salary) 합계급여, count(*) 직원수
FROM employees
GROUP BY department_id
HAVING count(*)<>1
ORDER BY 부서;

Q) 부서별 평균연봉, 최대연봉을 구하여 출력하시오, 단 부서별 급여 합계가 25000이상인 경우에 한해서 출력하시오

SELECT department_id 부서,max(salary*12) 최대연봉, avg(salary*12) 평균연봉
FROM employees
GROUP BY department_id
HAVING sum(salary)>=25000
ORDER BY 부서;