▶ 그룹 함수
* 그룹 함수를 사용하는 경우 고려 사항
- NULL값은 무시된다.
- 반드시 단 하나의 값만을 반환한다.
- GROUP BY 설정 없이 일반 컬럼과 기술될 수 없다.
예제 1. 사원의 급여 평균을 검색한다.
SELECT AVG(sal) 평균급여, ROUND(AVG(sal)) 평균급여
FROM emp;
평균급여 평균급여
---------- ----------
3167.57143 3168
예제 2. 사원들에게 지급된 보너스 총합과 보너스 평균을 검색한다.
SELECT SUM(comm) 총액,
ROUND(AVG(comm)) 평균, COUNT(comm) 수령인원,
ROUND(AVG(NVL(comm,0))) 환산평균, COUNT(*) 전체인원
FROM emp;
총액 평균 수령인원 환산평균 전체인원
---------- ---------- ---------- ---------- ----------
14780 869 17 704 21
SELECT COUNT(*) FROM emp
WHERE comm IS NOT NULL;
COUNT(*)
----------
17null 값은 무시가 되기 때문에 위 방식은 문제가 발생한다.
예제 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');
사번 이름 부서번호
------- ---------- ---------
0001 안영희 01보통은 위의 두 방식 중 첫번째 방식처럼 사용한다. 단일행 서브쿼리로 사용한다.
▶ 그룹 함수와 GROUP BY절
SELECT [DISTINCT/ALL] 컬럼 or 그룹함수, ...
FROM 테이블
WHERE 조건
GROUP BY Group대상
ORDER BY 정렬대상 [ASC/DESC]- 통계정보를 출력할 경우 같은 정보를 가진 사람끼리 통계정보를 출력하기 위해 GROUP BY 절을 사용한다.
- GROUP BY 절에 있는 컬럼 명을 SELECT절 일반컬럼에 꼭 넣어준다.
- 카디널리티: 값의 종류 (ex. 성별은 카디널리티 낮음, 이름은 카디널리티 높음)
예제 4. 업무별 평균 급여, 평균 연봉과 부서별 평균 연봉을 검색한다.
SELECT job 업무, ROUND(AVG(sal)) 평균_급여,
ROUND(AVG(sal*12+NVL(comm,0))) 평균_연봉
FROM emp
GROUP BY job;
업무 평균_급여 평균_연봉
------- ---------- ----------
분석 2900 35500
지원 3803 47467
개발 2782 34282
모델링 4300 51883
경영 4800 57600
회계 2380 28790
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;
부서번호 부서명 평균_연봉
--------- ------- ----------
01 총무 49833
02 회계 27990
10 ERP 42664
20 ISP 38768
30 ITEA 43323
40 CRM 25200dno만 그룹바이 해도 되지만 dname를 한 이유는 select절의 일반컬럼에 dno, dname 둘다 있기 때문에 dname도 그룹바이절에 적어준다.
예제 5. 부서별로 급여 평균의 최대 값과 최소 값을 검색한다.
SELECT dno 부서번호,
MAX(AVG(sal)) 최대평균, MIN(AVG(sal)) 최소평균
FROM emp
GROUP BY dno;
SELECT dno 부서번호,
*
1행에 오류:
ORA-00937: 단일 그룹의 그룹 함수가 아닙니다
SELECT MAX(AVG(sal)), MIN(AVG(sal))
FROM emp
GROUP BY dno;
MAX(AVG(SAL)) MIN(AVG(SAL))
------------- -------------
4070 2100dno로 그룹바이해도 select 절에 컬럼이 하나씩밖에 안나오는 그룹함수를 적었기 때문에 select 절에 dno를 뺀다.
예제 6. 그룹 대상 컬럼과 그룹 함수를 이용한 검색 결과 확인
SELECT dno 부서번호, COUNT(*) 인원수
FROM emp
WHERE job != '개발'
GROUP BY dno, job;
부서번호 인원수
--------- ----------
10 1
02 1
20 1
20 1
01 1
01 2
02 4
부서번호 인원수
--------- ----------
10 1
30 1
10 2
20 1
예제 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;
DNO DNAME ENO ENAME SAL
------- ------- ------- ---------- ----------
01 총무 0202 손하늘 3510
02 회계 0309 김선유 900
10 ERP 0269 권나현 2600
20 ISP 2002 제갈민 1520
30 ITEA 2007 이초록 1989
40 CRM 2008 윤고은 2100
실습
1. 각 학과별 학생 수를 검색한다.
SELECT major 학과, COUNT(sno)
FROM student
GROUP BY major;
학과 COUNT(SNO)
------- ----------
화학 17
유공 18
생물 17
식영 19
물리 16
2. 화학과와 생물학과 학생 4.5 환산 평점의 평균을 각각 검색한다.
SELECT major 학과, TO_CHAR(AVG(avr*4.5/4), '90.99') "4.5 환산평균"
FROM student
WHERE major IN ('화학', '생물')
GROUP BY major;
학과 4.5 환산평균
------- ------------
화학 2.45
생물 2.76
3. 부임일이 10년 이상 된 직급별(정교수, 조교수, 부교수) 교수의 수를 검색한다. ♠♠♠
SELECT orders 직위, COUNT(orders) "부임일이 10년 이상 교수 수"
FROM professor
WHERE (TO_CHAR(sysdate, 'YYYY')-TO_CHAR(hiredate, 'YYYY')+1)>=10
GROUP BY orders;
직위 부임일이 10년 이상 교수 수
------- --------------------------
조교수 7
정교수 8
부교수 9
4. 과목명에 화학이 포함된 과목의 학점수 총합을 검색한다.
SELECT cname 과목이름, SUM(st_num) 학점수총합
FROM course
WHERE cname LIKE '%화학%'
GROUP BY cname;
과목이름 학점수총합
------------ ----------
무기화학 2
환경화학 2
일반화학실험 2
일반화학 3
핵화학 3
식품화학 3
유기화학 3
5. 화학과 학생들의 기말고사 성적을 성적순으로 검색한다. //같은 사람이 여러번 나오는데... 어떻게 하징... 평균내야하나...
SELECT major 학과, s.sno 학번, sname 이름, TO_CHAR(AVG(result), 90.99) 기말고사평균성적
FROM student s, score r, course c
WHERE major = '화학'
AND s.sno=r.sno AND r.cno=c.cno
GROUP BY major, s.sno, sname
ORDER BY TO_CHAR(AVG(result), 90.99) DESC;
학과 학번 이름 기말고사평균
------- ------- ---------- ------------
화학 915301 정동상 73.30
화학 925306 김재백 73.15
화학 905302 정욱상 72.85
화학 925305 황현정 72.63
화학 945303 남궁경아 70.86
화학 945314 이철윤 70.59
화학 915304 권보수 69.50
6. 학과별 기말고사 평균을 성적순으로 검색한다.
SELECT major 학과, TO_CHAR(AVG(result), 90.99) 기말고사평균
FROM student s, score r, course c
WHERE s.sno=r.sno AND r.cno=c.cno
GROUP BY major
ORDER BY TO_CHAR(AVG(result), 90.99) DESC;
학과 기말고사평균
------- ------------
식영 69.99
유공 69.56
화학 69.44
물리 68.92
생물 67.93
7. 30번 부서의 업무별 연봉의 평균을 검색한다.
(단 출력 양식은 소수이하 두 자리까지 통일된 형식으로 출력한다.)
SELECT dno 부서번호, job 업무, TO_CHAR(AVG(sal*12+NVL(comm,0))) 평균연봉
FROM emp
WHERE dno = 30
GROUP BY dno, job;
부서번호 업무 평균연봉
--------- ------- -----------
30 모델링 51600
30 개발 39184
8. 물리학과 학생 중에 학년별로 성적이 가장 우수한 학생의 평점을 검색한다.
SELECT major 학과, syear 학년, TO_CHAR(MAX(avr), 90.99) 성적
FROM student
WHERE major = '물리'
GROUP BY major, syear
ORDER BY syear;
학과 학년 성적
------- ---------- ------------
물리 1 3.20
물리 2 3.22
물리 3 4.00
물리 4 3.33
9. 학년별로 환산 평점의 평균값을 검색한다.
(단 출력 양식은 소수이하 두 자리까지 통일된 양식으로 출력한다.)
SELECT syear 학년, TO_CHAR(AVG(avr*4.5/4), 90.99) 성적
FROM student
GROUP BY syear
ORDER BY syear;
학년 성적
---------- ------------
1 3.13
2 2.97
3 3.08
4 2.85
10. 화학과 1학년 학생 중 평점이 평균 이하인 학생을 검색한다.
SELECT major 학과, syear 학년, sno 학번, sname 이름, TO_CHAR(avr, 90.99) 성적
FROM student
WHERE major='화학' AND syear=1
AND avr <= (SELECT AVG(avr) FROM student
WHERE major='화학' AND syear=1);
학과 학년 학번 이름 성적
------- ---------- ------- ---------- ------------
화학 1 945303 남궁경아 2.36
화학 1 945314 이철윤 2.22
'SQL 공부 기록' 카테고리의 다른 글
| SQL 18 - DML(INSERT, UPDATE, DELETE) 문의 이해 (0) | 2021.11.19 |
|---|---|
| (수정중) SQL 17 - 그룹 함수와 HAVING (0) | 2021.11.18 |
| DB 2 - 개체, 관계, 속성, 식별자 (1) | 2021.11.17 |
| DB 1 - 데이터베이스 및 데이터 모델 (0) | 2021.11.17 |
| SQL 15 - 단일 행 함수 : 변환 함수 (0) | 2021.11.16 |