그룹 함수
함수명
|
기능
|
max
|
값들 중에 최대 값을 반환한다.
max(컬럼)
|
min
|
값들 중에 최소 값을 반환한다.
min(컬럼)
|
avg
|
평균 값을 계산한다.
avg(컬럼)
|
count
|
반환된 행의 수를 계산한다.
count(컬럼 | *)
|
sum
|
합계를 계산한다.
sum(컬럼)
|
stdev
|
표준편차를 계산한다.
stdev(컬럼)
|
variance
|
분산을 계산한다.
variance(컬럼)
|
그룹 함수를 사용하는 경우 고려 사항
- NULL값은 무시된다.
- 반드시 단하나의 값만을 반환한다.
- group by 설정 없이 일반 컬럼과 기술될 수 없다.
예제 1. 사원의 급여 평균을 검색한다.
select avg(sal) 평균급여, round(avg(sal)) 평균급여
from
emp;
예제 2. 사원들에게 지급된 보너스 총합과 보너스 평균을 검색한다.
select sum(comm) 총액, round(avg(comm)) 평균, count(comm) 수령인원,
round(avg(nvl(comm,0))) 환산평균, count(*) 전체인원
from
emp;
예제 3. 10번 부서원들보다 급여가 높은 사원을 검색한다.
select eno 사번, ename 이름, dno 부서번호
from
emp
where
sal > (select max(sal) from emp where dno = '10');
select eno 사번, ename 이름, dno 부서번호
from
emp
where
sal > all(select sal from emp where dno='10');
그룹 함수와 group by절
select [distinct/all] 컬럼 or 그룹함수, …
from 테이블
where 조건
group by group 대상
order by 정렬대상 [asc/desc]
예제 4. 업무별 평균 급여, 평균 연봉과 부서별 평균 연봉을 검색한다.
select job 업무, round(avg(sal)) 평균_급여, round(avg(sal*12+nvl(comm,0)))
평균_연봉
from
emp
group
by job;
select d.dno 부서번호, dname 부서명, round(avg(sal*12+nvl(comm,0))) 평균_연봉
from
dept d, emp e
where
d.dno = e.dno
group
by d.dno, dname
order
by d.dno;
예제 5. 부서별로 급여 평균의 최대 값과 최소 값을 검색한다.
select dno 부서번호, max(avg(sal)) 최대평균, min(avg(sal)) 최소평균
from
emp
group
by dno;
예제 6. 그룹 대상 컬럼과 그룹 함수를 이용한 검색 결과 확인
select dno 부서번호, count(*) 인원수
from
emp
where job != '개발'
group
by dno, job;
예제 7. 각 부서별 최소 급여를 받는 사원의 정보를 검색한다.
select
d.dno, dname, eno, ename, sal
from
emp e, dept d
where
d.dno=e.dno
and(d.dno,
sal) IN (select dno, min(sal) from emp group by dno)
order
by d.dno;
실습
1. 각 학과별 학생 수를 검색한다.
select
major, count(*)
from
student
group
by major;
#count 함수를 이용하여 데이터의 갯수를 출력 가능
2. 화학과와 생물학과 학생 4.5 환산 평점의 평균을 각각 검색한다.
select major, avg(avr/4*4.5)
from
student
where major in ('화학','생물')
group
by major;
# 평균은 avg로 계산이 가능
3. 부임일이 10년 이상 된 직급별(정교수, 조교수, 부교수) 교수의 수를 검색한다.
select
orders, count(*)
from
professor
where
10 <= months_between(sysdate,hiredate)/12
group
by orders;
# count를 이용하여 갯수를 세고
# months_between 을 이용하여 달 수를 구하고 12로 나누면 년수가 됨.
4. 과목명에 화학이 포함된 과목의 학점수 총합을 검색한다.
select
sum(st_num)
from
course
where cname like ('%화학%');
#sum을 이용하여 총 학점수를 더 할 수 있다.
5. 화학과 학생들의 기말고사 성적을 성적순으로 검색한다.
select t.sname,
s.result
from
student t, score s
where
t.sno=s.sno
and t.major='화학'
order
by s.result desc;
6. 학과별 기말고사 평균을 성적순으로 검색한다.
select
t.major, avg(s.result)
from
score s, student t
where
s.sno=t.sno
group
by t.major
order
by avg(s.result) desc;
# avg 를 이용해서 평균을 구한다
# 조인을 해줘야 자료가 연동되기에 sno를 이용하여 조인한다.
7. 30번 부서의 업무별 연봉의 평균을 검색한다.
단, 출력 양식은 소수이하 두 자리까지 통일된 형식으로 출력한다.
select
e.job, to_char(avg(sal),'9999.00')
from
emp e, dept d
where
e.dno=d.dno
and e.dno='30'
group
by e.job;
# 부서명을 구하기 위해서 조인을 한다
# avg을 이용해서 평균을 구한다.
# to_char을 이용해서 형식을 통일한다.
8. 물리학과 학생 중에 학년별로 성적이 가장 우수한 학생의 평점을 검색한다.
select
syear, max(avr)
from
student
where major='물리'
group
by syear
order
by syear;
# avr 평점중에서 가장 우수한 점수를 구하기 위해 max 사용
9. 학년별로 환산 평점의 평균값을 검색한다.
select
syear, avg(avr)
from
student
group
by syear
order
by syear;
10. 화학과 1학년 학생 중 평점이 평균 이하인 학생을 검색한다.
select
sname, avr
from
student
where major = '화학'
and
syear=1
and avr < (select avg(avr) from student where major = '화학' and syear =1 group
by syear);
# 한참 헤맸다 syear로 묶어줘야 값이 두개 나온다 avr로 묶으면 4개가 나옴.
# 평균값을 먼저 구하는 쿼리를 구하는게 더 쉽게 쿼리를 짤 수 있다.
댓글 없음:
댓글 쓰기