1. 그룹함수(집계 함수)
MAX
값들 중 최대값을 반환
>> MAX(컬럼)MIN
값들 중 최소값을 반환
>> MIN(컬럼)AVG
평균 값 계산
>> AVG(컬럼)COUNT
반환된 행의 개수 계산
>> COUNT (컬럼 | * )
행의 개수이므로 컬럼을 지정하는 것이 큰 의미가 없다.
' * ' 로 사용하는것이 일반적임.SUM
합계 계산
>> SUM(컬럼)STDDEV
표준편차 계산
VARIANCE
분산 계산
LISTAGG
구분자를 이용하여 데이터를 횡으로 나열할 수 있는 함수
>> listagg(ename, '; ') WITHIN GROUP (ORDER BY ename DESC) "Ename",그룹함수를 사용하는 경우 고려사항
- NULL값은 무시된다.(값이 NULL인 행은 제외하고 수행)
- 반드시 단 하나의 값만을 반환한다.
- GROUP BY열을 SELECT 절에 포함시키지 않아도 된다.
- 하나 이상의 GROUP BY 열을 나열하여 그룹에 대한 요약 결과를 조회할 수 있다.
- GROUP BY 설정 없이 일반 컬럼과 기술될 수 없다.
- SELECT절의 일반컬럼과 GROUP BY절의 일반컬럼은 개수가 동일해야 한다.
SELECT절의 일반컬럼 개수가 더 적으면 조회는 되지만 내용을 식별하기가 어려워지고 (내용상의 오류가 발생할 수 있음),
GROUP BY절의 일반컬럼 개수가 더 적으면 오류 발생
EXERCISE 1
사원의 급여 평균 검색
SELECT AVG(SAL) 평균급여, ROUND(AVG(SAL)) 평균급여
FROM EMP;EXERCISE 2
사원들에게 지급된 보너스 총합과 보너스 평균 검색
SELECT SUM(COMM) 총액, ROUND(AVG(COMM)) 평균 --NULL값인 행이 모두 제외됨
,COUNT(COMM) 수령인원, ROUND(AVG(NVL(COMM, 0))) 환산평균
,COUNT(*)
FROM EMP; NULL값을 포함하는 것과 제외하는 것의 차이 확인하기!
EXERCISE 3
10번 부서원들보다 급여가 높은 사원 검색
SELECT ENO, ENAME, ENO
FROM EMP
WHERE SAL>(SELECT MAX(SAL) FROM EMP WHERE DNO= '10')
AND DNO != '10';2. 그룹함수와 GROUP BY절
SELECT [DISTINCT | ALL] 컬럼 OR 그룹함수,...
FROM 테이블
WHERE 조건
GROUP BY GROUP_대상
ORDER BY 정렬대상 [ASC/DESC]
>> SELECT절에 그룹 함수와 컬럼이 같이 기술된 경우 해당 컬럼은 반드시 GROUP BY 절에 그룹화 되어야 한다.
>> SELECT절에 그룹 함수와 같이 쓰인 일반 컬럼이 GROUP BY 절에 기술되지 않으면 카디널리티가 일치하지 않아 에러 발생.
>> 예전에는 오름차순 정렬이 기본으로 제공되었지만 현재는 상황에 따라 다르다.
(ORDER BY 절을 DESC로 변경가능)
값이 정렬되길 원한다면 반드시 ORDER BY 절 추가한다.
EXERCISE 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
INNER JOIN EMP E ON D.DNO = E.DNO
GROUP BY D.DNO
,DNAME
ORDER BY D.DNO;EXERCISE 5
부서별로 급여 평균의 최대값과 최소값 검색
--부서별 평균 급여
SELECT DNO, AVG(SAL)
FROM EMP
GROUP BY DNO;
--부서별 최대/최소값
SELECT MAX(AVG(SAL)) 최대평균, MIN(AVG(SAL)) 최소평균
FROM EMP
GROUP BY DNO;위 쿼리 작성시 GROUP BY 절 없이 SELECT문에 DNO (일반컬럼)를 기술하면 오류가 발생한다.
(그룹함수는 일반 컬럼과 기술할 수 없음!!)
또, 위의 쿼리는 INLINE QUERY 로 기술할 수 있다.
SELECT MAX(평균), MIN(평균)
FROM (
SELECT DNO, AVG(SAL) 평균 --부서별 평균 구하는 쿼리를 FROM절 안에 삽입하여 테이블처럼 사용 >>FROM절 안에 또다른 쿼리가 들어간다.
FROM EMP
GROUP BY DNO
) A; --A : ALIAS인라인 쿼리는 부서별 평균 구하는 쿼리를 FROM절 안에 삽입하여 테이블처럼 사용한다.
FROM절 안에 또다른 쿼리를 삽입할 수 있다는 것이 핵심이다.
EXERCISE 6
그룹 대상 컬럼과 그룹 함수를 이용한 검색결과 확인
SELECT DNO 부서번호, JOB, COUNT(*) 인원수
FROM EMP
WHERE JOB != '개발'
GROUP BY DNO, JOB
ORDER BY DNO;위 쿼리에서 SELECT절의 일반컬럼 개수와, GROUP BY절의 일반컬럼 개수가 1:1로 매칭되지 않으면 오류가 발생한다.
EXERCISE 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;위 쿼리를 INLINE QUERY로 변경하기
SELECT A.DNO
,(SELECT C.DNAME FROM DEPT C WHERE C.DNO = A.DNO) DNAME
,A.ENO
,A.ENAME
,A.SAL
FROM EMP A
INNER JOIN (SELECT DNO, MIN(SAL) SAL
FROM EMP
GROUP BY DNO) B ON A.DNO = B.DNO
AND A.SAL = B.SAL
ORDER BY A.SAL DESC;실습
1. 각 학과별 학생 수를 검색
SELECT MAJOR 학과, COUNT(*) 학생수
FROM STUDENT
GROUP BY MAJOR;2. 화학과와 생물학과 학생들의 4.5 환산 평점의 평균 검색
SELECT MAJOR 학과, ROUND(AVG(AVR/4.0*4.5),2)환산평점
FROM STUDENT
WHERE MAJOR IN ('화학', '생물')
GROUP BY MAJOR;3. 부임일이 10년 이상 된 직급별(정교수, 조교수, 부교수) 교수의 수를 검색
SELECT ORDERS, COUNT(*)
FROM PROFESSOR
WHERE MONTHS_BETWEEN(SYSDATE, HIREDATE) >= 120
GROUP BY ORDERS;4. 과목명에 '화학'이 포함된 과목의 학점수 총합을 검색
SELECT SUM(ST_NUM)
FROM COURSE
WHERE CNAME LIKE '%화학%';
--일반컬럼이 없으므로 GROUP BY 절은 기술 X5. 화학과 학생들의 기말고사 성적을 성적순으로 검색
SELECT A.SNO
,SNAME
,MAJOR
,COUNT(*) CNT
,ROUND( AVG(RESULT))
,SUM(RESULT)
,MIN(RESULT)
,MAX(RESULT)
FROM STUDENT A, SCORE B
WHERE A.SNO = B.SNO
AND A.MAJOR = '화학'
GROUP BY A.SNO, SNAME, MAJOR
ORDER BY A.SNO ASC;6. 학과별 기말고사 평균을 성적순으로 검색
SELECT ST.MAJOR, ROUND(AVG(SC.RESULT),2) 평균
FROM STUDENT ST, SCORE SC
WHERE ST.SNO = SC.SNO
GROUP BY ST.MAJOR
ORDER BY 평균 ASC;7. 30번 부서의 업무별 연봉의 평균 검색
SELECT DNO 부서, JOB 업무, AVG(SAL) 연봉_평균
FROM EMP
WHERE DNO = '30'
GROUP BY DNO, JOB
ORDER BY 연봉_평균 ASC;8. 물리학과 학생중에 학년별로 성적이 가장 우수한 학생의 평점 검색
SELECT MAJOR 전공, SYEAR 학년, SNAME 이름, AVR 학점
FROM STUDENT
WHERE MAJOR = '물리'
AND (SYEAR, AVR) IN (SELECT SYEAR, MAX(AVR)
FROM STUDENT
WHERE MAJOR = '물리'
GROUP BY SYEAR)
ORDER BY AVR;9. 학년별로 환산 평점의 평균값
SELECT SYEAR, ROUND(AVG(AVR*4.5/4.0),2) 환산평균
FROM STUDENT
GROUP BY SYEAR
ORDER BY SYEAR;⭐⭐10. 화학과 1학년 학생 중 평점이 평균 이하인 학생을 검색
SELECT A.SNO, A.SNAME, A.SYEAR, A.AVR
,ROUND((SELECT AVG(AVR)
FROM STUDENT
WHERE MAJOR = '화학'
AND SYEAR = 1),2) "1학년 평균"
FROM STUDENT A
WHERE A.SYEAR = 1
AND A.MAJOR = '화학'
AND A.AVR <= (SELECT AVG(B.AVR)
FROM STUDENT B
WHERE B.SYEAR = 1
AND B.MAJOR = A.MAJOR);⭐⭐11. 선수테이블에서 팀별 각 포지션(FW, MF, DF, GK) 의 인원수와 팀 전체 인원수를 조회하되 데이터가 없는 경우 0 으로 표현. (team_id로 내림차순 정렬)
SELECT TEAM_ID,
,NVL(SUM(CASE POSITION WHEN 'FW' THEN 1 ELSE 0 END), 0) AS FW
,NVL(SUM(CASE POSITION WHEN 'MF' THEN 1 ELSE 0 END), 0) AS MF
,NVL(SUM(CASE POSITION WHEN 'DF' THEN 1 ELSE 0 END), 0) AS DF
,NVL(SUM(CASE POSITION WHEN 'GK' THEN 1 ELSE 0 END), 0) AS GK
,COUNT(*) AS 팀인원수
FROM PLAYER
GROUP BY TEAM_ID
ORDER BY TEAM_ID;
내가 보려고 쓰는 블로그