Oracle 기본정리 - Group By 기본편

Oracle 기본정리 - Group By 기본편

--실제로 테이블만들어서 연습해보자-

create table sungjuck
(
    hak number(3),
    ban number(3),
    hackno number(3),
    name varchar2(20),
    kor number(3)
);

insert into sungjuck values(1,1,1,'hong',50);
insert into sungjuck values(1,1,2,'hong1',30);
insert into sungjuck values(1,1,3,'hong2',40);
insert into sungjuck values(1,2,4,'hong3',50);
insert into sungjuck values(1,2,1,'hong4',60);
insert into sungjuck values(1,2,2,'hong5',70);
insert into sungjuck values(1,2,3,'hong6',80);
insert into sungjuck values(1,3,4,'hong7',90);
insert into sungjuck values(2,3,5,'hong8',10);
insert into sungjuck values(2,3,1,'hong9',24);
insert into sungjuck values(2,3,2,'hong10',53);
insert into sungjuck values(2,3,3,'hong44',60);

--sungjuck table 평균

select avg(kor) from sungjuck;

--group by 사용법

--selelct A, B, function(A or B) form table group by A, B

--예제1 : 학년별 평균

select avg(kor) from sungjuck group by hak;

--예제2 : 반 별 성적 평균

select ban,avg(kor) from sungjuck group by ban;

--예제3 : 학년 반 별 성적 평균

select hak,ban,avg(kor) from sungjuck group by hak,ban;

--예제4 : 학년 반 별 성적 평균 오름차순

select hak,ban,avg(kor) from sungjuck group by hak,ban order by hak,ban;

-------------------------------------------emp테이블 group by 예제---------------------------------
--부서별 급여평균

select deptno, avg(sal) from emp group by deptno

--부서별 comm 카운터수

select deptno, count(*), count(comm) from emp group by deptno;

--having 사용법

select deptno, avg(sal) from emp group by deptno;
select deptno, avg(sal) from emp where deptno=20 group by deptno;
select deptno, avg(sal) from emp group by deptno having deptno=20;

--부서별 sal의 최대값이 2900 이상인 경우

select deptno,max(sal),min(sal) from emp group by deptno having max(sal)>=2900;

--부서별 연도별 평균 급여를 출력

select * from emp;
select deptno,to_char(hiredate,'yyyy') ,avg(sal) from emp group by deptno,to_char(hiredate,'yyyy') order by deptno;

--manager , analyst 빼고 나머지 직업은 OTHER로 표시하기

방법1

select job,decode(job,'MANAGER','MANAGER','ANALYST','ANALYST','OTHER') as job2, sal from emp;

방법2

select decode(job,'MANAGER','MANAGER','ANALYST','ANALYST','OTHER') as job2, avg(sal) from emp group by decode(job,'MANAGER','MANAGER','ANALYST','ANALYST','OTHER');

--부서별 출력

select deptno, decode(deptno,10,deptno,0) as d10, decode(deptno, 20, deptno,0) as d20,decode(deptno, 30, deptno,0) as d30
from emp;

--부서별 카운터

select count(deptno), count(decode(deptno,10,deptno)) as d10, count(decode(deptno, 20, deptno)) as d20,count(decode(deptno, 30, deptno)) as d30
from emp;

--부서별 평균 급여 출력

select * from emp;
select avg(decode(deptno,10,sal)) as ten,avg(decode(deptno,20,sal)) as twe,avg(decode(deptno,30,sal))as th from emp;

--년도별 평균 급여 출력

select deptno,round(avg(sal)),round(avg(decode(to_char(hiredate,'yy'),'80',sal))) as zero, round(avg(decode(to_char(hiredate,'yy'),'81',sal))) as one, round(avg(decode(to_char(hiredate,'yy'),'82',sal)))as two from emp group by deptno;

--부서별, 입사년도별 인원 decode(스위치값(조건) , if, 값)

select deptno,count(*),count(decode(to_char(hiredate,'yy'),'80',sal)) as "80", count(decode(to_char(hiredate,'yy'),'81',sal)) as "81", count(decode(to_char(hiredate,'yy'),'82',sal))as "82" from emp group by deptno;