[SQL🔒] sum(case when ~ than ~ else ~ end) 구문 사용법 / 조건 합계 구하기

📌문법

sum(case when 조건 than 값 else (생략가능) end)

---

📑사용 문제 / 2024년 04월 25일 - 실기연습 (사원관리)

  🔨 문제

📎 사원 급여 조회

사원번호	사원명	부서	직위	근무지	실적	입사일
E001	김태형	인사	부장	대전영업소	C등급	3,675,000
E002	박지민	총무	과장	청주영업소	C등급	3,150,000
E003	정호석	총무	대리	청주영업소	D등급	2,500,000
E004	민윤기	영업	사원	충남본부	B등급	2,160,000

📎 부서별 급여 조회

부서코드	부서명	총급여
01	인사	3,675,000원
02	총무	5,650,000원
03	영업	2,160,000원

📎 지역별 급여 조회

지역	자역명	총급여
31	대전영업소	3,675,000원
32	청주영업소	5,650,000원
40	충남본부	2,160,000원

📎 급여 산출 조건
  기본급 + 실적(보너스)
  (실적 보너스 : D:보너스없음, C:기본급5%, B:8%, A:10%)

  🔨 테이블 및 데이터

📎 테이블

create table tbl_company_employee (
    e_no char(4) not null,
    e_name varchar2(20) not null,
    e_depart char(2) not null,
    e_level char(2) not null,
    e_work_place char(2),
    performance char(1),
    e_join_date char(8) not null,
    constraint tbl_company_employee_pk primary key (e_no)
);

create table tbl_company_depart (
    depart_code char(2) not null,
    d_name varchar2(20),
    constraint tbl_company_depart_pk primary key (depart_code)
);

create table tbl_company_level (
    level_code char(2) not null,
    l_name varchar2(20),
    salary number,
    constraint tbl_company_level_pk primary key (level_code)
);

create table tbl_company_work (
    work_place char(2) not null,
    w_name varchar2(20),
    constraint tbl_company_work_pk primary key (work_place)
);

📎 데이터

insert into tbl_company_employee values ('E001','김태형','01','20','31','C','20200825');
insert into tbl_company_employee values ('E002','박지민','02','30','32','C','20200825');
insert into tbl_company_employee values ('E003','정호석','02','40','32','D','20200825');
insert into tbl_company_employee values ('E004','민윤기','03','50','40','B','20200825');

insert into tbl_company_depart values ('01','인사');
insert into tbl_company_depart values ('02','총무');
insert into tbl_company_depart values ('03','영업');
insert into tbl_company_depart values ('04','생산');

insert into tbl_company_level values ('10','이사',4500000);
insert into tbl_company_level values ('20','부장',3500000);
insert into tbl_company_level values ('30','과장',3000000);
insert into tbl_company_level values ('40','대리',2500000);
insert into tbl_company_level values ('50','사원',2000000);

insert into tbl_company_work values ('31','대전영업소');
insert into tbl_company_work values ('32','청주영업소');
insert into tbl_company_work values ('33','공주영업소');
insert into tbl_company_work values ('40','충남본부');
insert into tbl_company_work values ('50','본사');

---

🔒 sum(case when ~ than ~ else ~ end) 구문을 이용한 풀이

  🔨 푸는 방법 생각하기

1. 출력해야할 것

• 사원 급여 조회
  e_no, e_name, performance, d_name, l_name, w_name, l.salary+(l.salary*(실적등급 보너스)) as e_salary

• 부서별 급여 조회
  d.depart_code, d.d_name, sum(l.salary+(l.salary*(실적등급 보너스))) as dept_salary

• 지역별 급여 조회
  w.work_place, w.w_name, sum(l.salary+(l.salary*(실적등급 보너스))) as work_salary
  
  

2. l.salary+(l.salary*(실적등급 보너스))를 case when ~ 이용하여 조건에 맞게 값 구하기

(case when e.performance = 'D' then l.salary -- 보너스 없음
    when e.performance = 'C' then l.salary+(l.salary*0.05) -- 0.5%
	when e.performance = 'B' then l.salary+(l.salary*0.08) -- 0.8%
	when e.performance = 'A' then l.salary+(l.salary*0.1) -- 1%
end) as 컬럼명

3. 위에서 만든 컬럼 select구절에 넣기

4. Join 문장 완성

  🔨 정답

📎 사원 급여 조회

select e.e_no,
        e.e_name,
        d.d_name,
        l.l_name,
        w.w_name,
        e.performance,
        sum(case when e.performance = 'D' then l.salary 
                when e.performance = 'C' then l.salary+(l.salary*0.05) 
				when e.performance = 'B' then l.salary+(l.salary*0.08)
				when e.performance = 'A' then l.salary+(l.salary*0.1)
			end ) as e_salary
from tbl_company_employee e,
        tbl_company_depart d,
        tbl_company_level l,
        tbl_company_work w
where e.e_depart = d.depart_code and
        e.e_level = l.level_code and
        e.e_work_place = w.work_place
group by e.e_no,e.e_name,d.d_name,l.l_name,w.w_name,e.performance
order by e.e_no;

부서별 급여 조회

select d.depart_code,
        d.d_name,
        sum(case when e.performance = 'D' then l.salary
            	when e.performance = 'C' then l.salary+(l.salary*0.05)
            	when e.performance = 'B' then l.salary+(l.salary*0.08)
            	when e.performance = 'A' then l.salary+(l.salary*0.1)
            end) as dept_salary
from tbl_company_employee e,
        tbl_company_depart d,
        tbl_company_level l
where e.e_depart = d.depart_code and
        e.e_level = l.level_code
group by d.depart_code, d.d_name
order by d.depart_code;

지역별 급여 조회

select w.work_place,
        w.w_name,
        sum(case when e.performance = 'D' then l.salary
            	when e.performance = 'C' then l.salary+(l.salary*0.05)
            	when e.performance = 'B' then l.salary+(l.salary*0.08)
            	when e.performance = 'A' then l.salary+(l.salary*0.1)
            end) as work_salary
from tbl_company_employee e,
        tbl_company_work w,
        tbl_company_level l
where e.e_work_place = w.work_place and
        e.e_level = l.level_code
group by w.work_place, w.w_name
order by w.work_place;