<29, 30> GROUP

 

[그룹 함수]
; 검색된 여러 행을 이용하여 통계정보를 계산하는 함수

MAX  값들 중에 최대값을 반환
MIN  값들 중에 최소값을 반환
AVG  평균값을 계산
COUNT 반환된 행의 수를 계산
SUM  총합을 계산
STDDEV 표준편차를 계산
VARIANCE 분산을 계산

1) null 값은 무시된다     - null : 알 수 없다.. 0값이 아님! 그래서 치환을 함
2) 반드시 1개의 값만을 반환한다
3) 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))) "환산 평균", -- null값을 0으로 치환해서 평균냄
       COUNT(*) 전체인원                   -- emp 전체 행 개수
FROM emp;


3) 보너스에서 null이 아닌 사람 수 계산하세요
SELECT COUNT(*)
FROM emp;       --14개

SELECT COUNT(*)
FROM emp
WHERE comm IS NOT NULL;

SELECT COUNT(*)
FROM emp
WHERE comm IS NULL;



[그룹 함수와 GROUP BY 절]

SELECT 컬럼 OR 그룹 함수...
FROM 테이블
WHERE 조건
GROUP BY 그룹대상
ORDER BY 정렬대상;

1) 그룹함수와 함께 사용되는 컬럼은 
--  반드시 GROUP BY 절에 기술되어야 한다
2) GROUP BY 절에 기술되지 않으면
--   ORA-00937 에러가 발생한다

3) 업무별 평균 급여, 평균 연봉을 검색한다
--ORA-00937 : 카디널리티가 일치하지 않는다
--AVG 함수는 1개의 결과값
--job 은 행개수만큼 결과값

SELECT job 업무, ROUND(AVG(sal)) 평균급여,
       ROUND(AVG(sal*12+NVL(comm, 0))) 평균연봉
FROM emp;
--  -->> 2) GROUP BY 절에 기술되지 않으면 ORA-00937 에러가 발생한다


SELECT job 업무
FROM emp;       -- 결과 행의 개수 14

SELECT ROUND(AVG(sal)) 평균급여,
       ROUND(AVG(sal*12+NVL(comm, 0))) 평균연봉
FROM emp;       -- 결과 행의 개수 1 : 그룹함수니까

SELECT job 업무, ROUND(AVG(sal)) 평균급여,
       ROUND(AVG(sal*12+NVL(comm, 0))) 평균연봉
FROM emp
GROUP BY job;     -- 일반컬럼 + 그룹함수 : 업무별로 평균을 구하겠다. 그래서 그룹으로 묶어야함..


4) 그룹 함수와 함께 사용되는 일반 컬럼은
반드시 GROUP BY 에 기술되어야 한다


5) 부서별 평균 급여, 평균 연봉을 검색한다
SELECT d.dno 부서번호, dname 부서명,
       ROUND(AVG(sal)) 평균급여,
       ROUND(AVG(sal*12+NVL(comm, 0))) 평균연봉
FROM dept d, emp e
WHERE d.dno=e.dno
GROUP BY d.dno, dname;

--TO_CHAR(AVG(result), '99.99') "기말 평균"

 

연습문제

1) 3학년 학생의 학과별 평점 평균과 분산 및 편차를 검색하세요
--STDDEV 표준편차를 계산
--VARIANCE 분산을 계산
SELECT major, ROUND(AVG(avr), 2) 평점평균,
              ROUND(VARIANCE(avr), 2) 분산, 
              ROUND(STDDEV(avr), 2) 표준편차
FROM student
WHERE syear='3'
GROUP BY major;


2) 화학과 학년별 평균 평점을 검색하세요
SELECT syear 학년, TO_CHAR(AVG(avr), 9.99)
FROM student
WHERE major ='화학'
GROUP BY syear;


3) 각 학생별 기말고사 평균을 검색하세요
SELECT s.sno, sname, ROUND(AVG(result))
FROM student s, score r
WHERE s.sno=r.sno
GROUP BY s.sno, sname;

SELECT sno, sname, ROUND(AVG(result))
FROM student
NATURAL JOIN score
GROUP BY sno, sname;


4) 각 학과별 학생 수를 검색하세요
SELECT major 학과, COUNT(*) 학생수
FROM student
GROUP BY major;



5) 화학과와 생물학과 학생 4.5 환산 평점의 평균을 각각 검색하세요

SELECT sno, sname, major 학과, ROUND(AVG(avr*1.125), 2) 성적
FROM student
WHERE major='화학' OR major='생물'
GROUP BY sno, sname, major;

SELECT major 학과, ROUND(AVG(avr*1.125), 2) 성적
FROM student
WHERE major='화학' OR major='생물'
GROUP BY major;


6) 부임일이 10년 이상 된 직급별(정교수, 조교수, 부교수) 교수의 수를 
   검색하세요
SELECT orders 직급, COUNT(*)   
FROM professor
WHERE sysdate > hiredate+3650
GROUP BY orders;

   
7) 과목명에 화학이 포함된 과목의 학점수 총합을 검색하세요
SELECT SUM(st_num) "화학 학점총합"
FROM course
WHERE cname LIKE '%화학%';

8) 화학과 학생들의 기말고사 성적을 성적순으로 검색하세요
SELECT sno, sname, result
FROM score
NATURAL JOIN student
WHERE major='화학'
--GROUP BY sno, sname, result
ORDER BY result DESC;


9) 학과별 기말고사 평균을 성적순으로 검색하세요
SELECT major 학과, ROUND(AVG(result), 2)
FROM score
NATURAL JOIN student
GROUP BY major
ORDER BY ROUND(AVG(result), 2) DESC;

​