Oracle/ 그룹함수와 GROUP BY절

<그룹함수>

검색된 여러 행을 이용하여 통계 정보를 계산하는 함수

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

1) NULL값은 무시된다
2) 반드시 한개의 값을 반환
3) GROUP BY 없이 일반 컬럼과 기술될 수 없다.

--사원의 평균 급여검색
SELECT AVG(sal) 평균급여, ROUND(AVG(sal)) 평균급여
FROM emp;


--사원들에게 지급된 보너스 총합과 보너스 평균을 검색
SELECT SUM(comm)"보너스총액",
           ROUND(AVG(comm)) 평균,
           COUNT(comm) 수령인원,
           ROUND(AVG(NVL(comm,0))) 환산평균,
           COUNT(*) 전체인원
FROM emp;

<그룹함수와 GROUP BY절>

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

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

--업무별 평균 급여, 평균 연봉을 검색한다
--ORA-00937 : 카디널리티가 일치하지 않는다
--AVG 함수는 1개의 결과값
--job 은 행개수만큼 결과값
SELECT job 업무, ROUND(AVG(sal)) 평균급여,
       ROUND(AVG(sal*12+NVL(comm,0))) 평균연봉
FROM emp; --오류



--그룹 함수와 함께 사용되는 일반 컬럼은
--반드시 GROUP BY 에 기술되어야 한다
SELECT job 업무, ROUND(AVG(sal)) 평균급여,
       ROUND(AVG(sal*12+NVL(comm,0))) 평균연봉
FROM emp
GROUP BY job;



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


SELECT d.dno 부서번호, dname 부서명,
       ROUND(AVG(sal)) 평균급여,
       ROUND(AVG(sal*12+NVL(comm,0))) 평균연봉
FROM emp e
JOIN dept d ON e.dno=d.dno
GROUP BY d.dno, dname
ORDER BY 평균연봉 DESC;


SELECT COUNT(*) "전체 인원"
FROM emp;  -- 12

SELECT COUNT(comm) "보너스 수령 인원"
FROM emp;  -- 9

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

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

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

--2. 화학과 학년별 평균 평점을 검색하라
SELECT major, syear, AVG(avr) 평점평균 
FROM student
WHERE major = '화학'
GROUP BY major, syear
ORDER BY syear;

--3. 각 학생별 기말고사 평균을 검색하라
SELECT student.sno, sname, TO_CHAR(AVG(result), '99.99') 기말평균
FROM student
LEFT JOIN score ON student.sno=score.sno
GROUP BY sname, student.sno
ORDER BY 기말평균 DESC;

--4. 각 학과별 학생 수를 검색하라
SELECT major, COUNT(sno)
FROM student
GROUP BY major;

--5. 화학과와 생물학과 학생 4.5 환산 평점의 평균을 각각 검색하라
SELECT major, TO_CHAR(AVG(avr/8*9), '99.99') 환산평점평균 
FROM student
WHERE major = '화학' OR major = '생물'
GROUP BY major;

--6. 부임일이 10년 이상 된 직급별(정교수, 조교수, 부교수)교수의 수를 검색하라
--  WHERE MONTHS_BETWEEN(sysdate, hiredate) >= 120;
SELECT orders, COUNT(hiredate)
FROM professor
WHERE MONTHS_BETWEEN(sysdate, hiredate) >= 120
GROUP BY orders;

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

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

--9. 학과별 기말고사 평균을 성적순으로 검색하라
SELECT major, TO_CHAR(AVG(result), '99.99')  기말고사성적
FROM student
JOIN score ON student.sno=score.sno
GROUP BY major
ORDER BY 기말고사성적 DESC;

all the records

this is a blog for my web development study. github: https://github.com/MJJng

Oracle/ 그룹함수와 GROUP BY절

티스토리툴바