[SQL] GROUP BY : CASE WHEN을 이용하여 피봇팅하기

기존 테이블

select * from hr.emp;

 

 

취업연도 별 평균 급여 구하기

 select to_char(hiredate, 'yyyy') as hire_year
      , avg(sal) as avg_sal
   from hr.emp
  group by to_char(hiredate, 'yyyy')
  order by 1;

 

 

직업 별 인원 구하기

'SALESMAN'은 'SALESMAN'으로 그 외는 'OTHERS'로 분류하고 인원 구하기

 select case when job = 'SALESMAN' then 'SALESMAN' else 'OTHERS' end as job_gubun
      , count(*)
   from hr.emp
  group by case when job = 'SALESMAN' then 'SALESMAN' else 'OTHERS' end;

 

 

각 직업 급여 합계 구하기

피봇팅 하여 구하기

select 
    sum(case when job = 'SALESMAN' then sal end) as sales_sum
  , sum(case when job = 'MANAGER' then sal end) as manager_sum
  , sum(case when job = 'ANALYST' then sal end) as analyst_sum
  , sum(case when job = 'CLERK' then sal end) as clerk_sum
  , sum(case when job = 'PRESIDENT' then sal end) as president_sum
  from hr.emp;

 

 

부서 별 각 직업 급여 합계 구하기

select deptno 
     , sum(sal) as sal_sum
	 , sum(case when job = 'SALESMAN' then sal end) as sales_sum
	 , sum(case when job = 'MANAGER' then sal end) as manager_sum
	 , sum(case when job = 'ANALYST' then sal end) as analyst_sum
	 , sum(case when job = 'CLERK' then sal end) as clerk_sum
	 , sum(case when job = 'PRESIDENT' then sal end) as president_sum
  from hr.emp
 group by deptno ;

 

 

출처

https://www.inflearn.com/course/%EB%8D%B0%EC%9D%B4%ED%84%B0%EB%B6%84%EC%84%9D-sql-%ED%8E%80%EB%8D%94%EB%A9%98%ED%83%88/dashboard

데이터 분석 SQL Fundamentals - 인프런 | 강의