SQL_04_문제풀이(집계함수, group by, having, with rollup, grouping)

집계함수

 

-- TODO:  커미션 비율(comm_pct)이 있는 직원의 수를 조회
select count(comm_pct)
from emp;

select comm_pct from emp;

-- TODO: 커미션 비율(comm_pct)이 없는 직원의 수를 조회
select count(*) - count(comm_pct) as '커미션 비율이 없는 직원의 수'
from emp;

select count(*) from emp where comm_pct is null;

-- TODO: 가장 큰 커미션비율(comm_pct)과 과 가장 적은 커미션비율을 조회
select max(comm_pct),
	   min(comm_pct)
from emp;	

-- TODO:  커미션 비율(comm_pct)의 평균을 조회. 소수점 이하 2자리까지 출력
select round(avg(comm_pct), 2)
from emp;

-- TODO: 직원 이름(emp_name) 중 사전식으로 정렬할때 가장 나중에 위치할 이름을 조회.
select max(emp_name)
from emp;


-- TODO: 급여(salary)에서 최고 급여액과 최저 급여액의 차액을 출력
select max(salary) - min(salary) 차액
from emp;

-- TODO: 가장 긴 이름(emp_name)이 몇글자 인지 조회.
select max(char_length((emp_name))) from emp;

select emp_name from emp;

-- TODO: EMP 테이블의 업무(job) 종류가 몇개 있는 조회. 고유값들의 개수
select count(distinct(job)) from emp;
select job from emp;

-- TODO: EMP 테이블의 부서(dept_name)가 몇종류가 있는지 조회. 고유값들의 개수
select count(distinct(dept_name)) -- null 빼고 계산 -> null은 범주값(고유값)에 포함 안됨
	   , count(distinct ifnull(dept_name, "미배치")) -- null을 포함해서 계산, null이 범주값에 포함된 경우
from emp; 

select distinct ifnull(dept_name, "미배치") from emp;

---

group by 절

 

-- TODO: 부서별(dept_name) 직원수를 조회
select dept_name 부서명,
	   count(*) 직원수
from emp
group by dept_name;

-- TODO: 업무별(job) 직원수를 조회. 직원수가 많은 것부터 정렬.
select job 업무,
	   count(*) 직원수
from emp
group by job
order by 2 desc;       


-- TODO: 부서명(dept_name), 업무(job)별 직원수, 최고급여(salary)를 조회. 부서이름으로 오름차순 정렬.
select dept_name 부서명,
	   job 업무,
       max(salary) 최고급여
from emp
group by dept_name, job
order by dept_name;       

-- TODO: EMP 테이블에서 입사연도별(hire_date) 총 급여(salary)의 합계을 조회. 
-- (급여 합계는 정수부에 자리구분자 , 를 넣고 $를 붙이시오. ex: $2,000,000)
select year(hire_date) 입사연도,
	   concat("$", format(sum(salary), 2)) as "총 급여의 합계"
from emp
group by year(hire_date)
order by 1;       

-- TODO: 같은해 입사해서 같은 업무를 한 직원들의 평균 급여(salary)을 조회
select year(hire_date) 입사연도,
	   job 업무,
       format(avg(salary), 2) as "평균 급여"
from emp
group by year(hire_date), job
order by 1;     

-- TODO: 부서별(dept_name) 직원수 조회하는데 부서명(dept_name)이 null인 것은 제외하고 조회.
select dept_name 부서명,
	   count(*) 직원수
from emp
where dept_name is not null
group by dept_name;       

-- TODO 급여 범위별 직원수를 출력. 급여 범위는 5000 미만, 5000이상 10000 미만, 10000이상 20000미만, 20000이상. 
-- case 문 이용
select case when salary < 5000 then "5000미만"
			  when salary < 10000 then "5000이상 10000미만"
              when salary < 20000 then "10000이상 20000미만"
              else "20000이상" end as "급여범위",
		count(*) 직원수
from emp
group by case when salary < 5000 then "5000미만"
			  when salary < 10000 then "5000이상 10000미만"
              when salary < 20000 then "10000이상 20000미만"
              else "20000이상" end;

---

having 절

 

-- TODO: 20명 이상이 입사한 년도와 (그 해에) 입사한 직원수를 조회.
select year(hire_date) 입사연도,
	   count(*) 직원수
from emp
group by year(hire_date)
having count(*) >= 20
order by 1;       

-- TODO: 10명 이상의 직원이 담당하는 업무(job)명과 담당 직원수를 조회
select job 업무,
	   count(*) 직원수
from emp
group by job
having count(*) >= 10;       

-- TODO: 평균 급여가(salary) $5000 이상인 부서의 이름(dept_name)과 평균 급여(salary), 직원수를 조회
select dept_name 부서명,
	   round(avg(salary), 2) as "평균 급여",
       count(*) 직원수
from emp
group by dept_name
having avg(salary) >= 5000
order by 2;         

-- TODO: 평균급여가 $5,000 이상이고 총급여가 $50,000 이상인 부서의 부서명(dept_name), 평균급여와 총급여를 조회
select dept_name 부서명,
	   round(avg(salary), 2) 평균급여,
       format(sum(salary), 2) 총급여
from emp
group by dept_name
having avg(salary) >= 5000 and sum(salary) >= 50000
order by 2,3;       

-- TODO 직원이 2명 이상인 부서들의 이름과 급여의 표준편차를 조회
select dept_name 부서명,
	   count(*) 직원수,
	   round(stddev(salary), 2) 표준편차
from emp
group by dept_name
having count(*) >= 2
order by 2;       

-- TODO  커미션이 있는 직원들의 입사년도별 평균 급여를 조회. 단 평균 급여가 $9,000 이상인 년도분만 조회.
select year(hire_date) 입사년도,
	   avg(salary) 평균급여
from emp
where comm_pct is not null
group by year(hire_date)
having avg(salary) >= 9000;

---

with rollup, grouping

 

-- # 총계/소계 행의 경우 :  총계는 '총계', 중간집계는 '소계' 로 출력
-- TODO: 부서별(dept_name) 별 최대 salary와 최소 salary를 조회
select ifnull(dept_name, "총계") 부서명,
	   max(salary) as "최대 급여",
	   min(salary) as "최소 급여"
from emp
where dept_name is not null
group by dept_name with rollup;       

-- 강사님 버전
select  if(grouping(dept_name)=0, dept_name, '총계')  as dept_name,  -- 0: group 별 집계, 1: 전체 집계
		max(salary),
        min(salary), 
        max(salary) - min(salary) as "최대 최소 급여 차이"
from    emp
group by dept_name with rollup; 

-- TODO: 상사_id(mgr_id) 별 직원의 수와 총계를 조회하시오.
select ifnull(mgr_id,"총계") as "상사_id",
	   count(*) 직원수
from emp
where mgr_id is not null
group by mgr_id with rollup;       

-- 강사님 버전
select if(grouping(mgr_id) = 1, "총계", mgr_id) as "mgr_id",
	   count(*)
from emp
group by mgr_id with rollup;
       
-- TODO: 입사연도(hire_date의 year)별 직원들의 수와 연봉 합계 그리고 총계가 같이 출력되도록 조회.
SET sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY','')); -- rollup에 대한 결과값이 제대로 안나올 경우 실행

select if(grouping(year(hire_date)) = 1, "총계", year(hire_date)) as "입사년도",
	   count(*) 직원수,
	   format(sum(salary), 0) as "급여 합계"
from emp
group by year(hire_date) with rollup;       

-- TODO: 부서(dept_name), 입사년도별 평균 급여(salary) 조회. 부서별 집계와 총집계가 같이 나오도록 조회
select dept_name,
	   year(hire_date),
	   avg(salary)
from emp
group by dept_name, year(hire_date) with rollup;

-- 강사님 버전
select case grouping(dept_name, year(hire_date)) when 0 then concat(dept_name, "-", year(hire_date))
												 when 1 then dept_name
                                                 when 3 then "총계" end as "구분",
	   avg(salary)
from emp
group by dept_name, year(hire_date) with rollup;

-- grouping 따로 처리
select if(grouping(dept_name)=1, '총계', dept_name) as "dept_name",
	   if(grouping(year(hire_date))=1, '집계', year(hire_date)) as "입사년도",
       avg(salary)
from emp
group by dept_name, year(hire_date) with rollup;