210831_그룹함수

그룹함수

avg(salary) : salary의 평균

count(salary) : 행의 개수

max(salary) : salary의 최대값

min(salary) : salary의 최소값

stddev(salary) : salary의 표준편차

sum(salary) : salary의 총합

variance(salary) : salary의 분산

--기본적으로 이 함수들은 null값은 모두 무시하며 그룹함수는 where절로 제어할 수 없다.

--평균나이 구하기
select avg(age) from person;

--평균나이 구하기..소수점 1자리(round)
select round(avg(age),1) from person;

--나이 합계
select sum(age) from person;

--전체 인원 구하기..cnt 별칭 주기(띄어쓰기 할 거면 "" 사용)
select count(*) cnt from person;

--현재 날짜 콘솔에 출력
select sysdate from dual;

--내일날짜를 콘솔에 출력
select sysdate+1 from dual;

--일주일뒤 날짜를 콘솔에 출력
select sysdate+7 from dual;

 

--emp에서 sal의 합계
select sum(sal) 합계 from emp;

--sal에 대한 합계와 평균(소수점 이하 한자리 반올림)
select sum(sal) 합계, round(avg(sal),1) 평균 from emp;

--최대급여, 최소급여
select max(sal) 최대급여, min(sal) 최소급여 from emp;

--평균급여보다 더 많이 받는 사람들의 목록 출력..서브쿼리
select * from emp where sal>(select avg(sal) from emp);

--이름이 안영숙인 사람의 mgr과 같은 사람 출력..서브쿼리
select * from emp where mgr=(select mgr from emp where ename='안영숙');

--emp테이블에서 hdate가 2월인 사람만 출력
select * from emp where to_char(hdate, 'mm')='02';

--hdate가 92년생인 사람 출력
select * from emp where to_char(hdate, 'yy')='92';

 

 

~~~~~~~~~~~~~~~~~~~~~~~~~~~~

 

 

group by
--컬럼명을 group함수와 select절에 사용하고자 하는 경우 group by 뒤에 컬럼명 추가
--having절은 group 함수를 가지고 조건비교를 할 때 사용
--where - group by - having - order by 순

--emp테이블에서 부서별(dno)로 사원들의 평균급여 출력..group by
select dno,avg(sal) "평균 급여" from emp group by dno;

--emp테이블에서 부서별dno로 사원들의 평균커미션 출력
select dno,avg(NVL(comm,0)) "평균 커미션" from emp group by dno;

--emp에서 부서별로 그룹화해서 부서번호,인원수,급여평균,보너스합계 출략
select dno 부서번호, count(*) 인원수, avg(sal) 급여평균, sum(NVL(comm,0)) 보너스합계 from emp group by dno;

--emp에서 job별로 그룹화해서 인원수, 평균급여, 최고급여, 최저급여, 급여합계 출력
select job 직업, count(*) 인원수, avg(sal) 평균급여, max(sal) 최고급여, min(sal) 최저급여, sum(sal) 급여합계 from emp group by job;

--professor에서 학과별로 교수들의 평균 급여를 출력
select deptno 학과번호, avg(pay) 평균급여 from professor group by deptno;

--professor에서 학과별, 직급별로 교수들의 평균 급여 출력
select deptno 학과번호,position 직급, avg(pay) 평균급여 from professor group by deptno,position;

 

having
--having 절 : 조건 주고 검색
--where절은 집계함수 사용 불가능
--having절은 집계함수 가지고 조건비교할 때 사용하며 group by와 함께 사용함
--having절이 where절과 다른 것은 group by와 함께 사용해야 하며 집계함수를 사용하여 조건절을 작성하거나 grouop by컬럼만 조건절에 사용할 수있다
--그룹함수의 조건에서는 where절을 허가하지 않는다


--평균급여가 450인 이상인 부서의 부서번호와 평균급여 출력
--select deptno, avg(pay) from professor where avg(pay)>450 group by deptno; 불가능
select deptno, avg(pay) from professor group by deptno having avg(pay)>450;

--emp에서 sal이 1000이상인 부서의 부서번호와 평균급여 출력
select deptno, avg(sal) from emp group by deptno having avg(sal)>=1000;


--where절 group by 사용
--deptno가 10,20,30인 것의 부서의 개수 출력
select job,count(*) cnt from emp where deptno in('10','20','30') group by job;


--where절 group by 사용해서 having절로 조건
--deptno가 10,20,30인 것의 부서의 개수가 3개 이상일 때 출력
select job,count(*) cnt from emp where deptno in('10','20','30') group by job having count(*)>=3;

--deptno가 20인 job의 부서개수 출력
--where deptno in('10','20','30') : 10,20,30 중에서  라는 뜻
select job 부서, deptno 부서번호, count(*) 부서개수 from emp where deptno in('10','20','30') group by job, deptno having deptno='20';

--where,group,having 사용하여 job cnt sal 구하는데 조건은 그룹인원이 3명 이상이고 급여합계가 5000 초과
select job 부서, count(*) 인원수, sum(sal) 총급여 from emp where deptno in('10','20','30') group by job having sum(sal)>5000 and count(*)>=3;
--and,or조건도 가능