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;
'프로그래밍 정리 > Oracle' 카테고리의 다른 글
Oracle 기본정리 - scott계정 테이블사용하기(emp,dept....) (0) | 2019.11.29 |
---|---|
Oracle 기본정리 - Group By 응용편 (0) | 2019.11.29 |
Oracle 기본정리 - 수학 함수6(sum, avg, count) (0) | 2019.11.29 |
Oracle 기본정리 - 함수5(decode, case when) (0) | 2019.11.29 |
Oracle 기본정리 - 날짜,문자 함수4(sysdate, to_char, to_date, to_number, add_months, next_day) (0) | 2019.11.29 |