쌍용교육 -SQL 수업 18일차 그룹함수-(1.AVG,COUNT,2.MAX,MIN,SUM3.GROUP BY,4.HAVING5.실습문제)

AVG

AVG(): NULL을 제외한 모든 값들의 평균을 반환, NULL값은 평균 계산에서 무시됨
SELECT AVG(sal) FROM emp;
SELECT ROUND(AVG(sal)) FROM emp;

COUNT

COUNT() : NULL을 제외한 값을 가진 모든 레코드(행)의 수를 반환. count(*)형식을
                사용하면 NULL도 계산에 포함
SELECT COUNT(empno) FROM emp;
SELECT COUNT(comm) FROM emp; --null값이 포함되어서 4개가 나옴
SELECT COUNT(*) FROM emp;

MAX,MIN,SUM

MAX() : 레코드 내에 있는 여러 값 중 가장 큰 값을 반환
SELECT MAX(sal) FROM emp;
SELECT MAX(ename) FROM emp;
SELECT MAX(hiredate) FROM emp;

MIN() : 레코드 내에 있는 여러 값 중 가장 작은 값을 반환
SELECT MIN(sal) FROM emp;
SELECT MIN(ename) FROM emp;
SELECT MIN(hiredate) FROM emp;

SUM(): 레코드들이 포함하고 있는 모든 값을 더하여 반환
SELECT SUM(sal) FROM emp;

SELECT MAX(sal),MIN(sal),ROUND(AVG(sal)),SUM(sal) FROM emp;

SELECT MAX(sal),MIN(sal),ROUND(AVG(sal)),SUM(sal) 
FROM emp WHERE deptno = 10;

SELECT COUNT(*) FROM emp WHERE deptno = 20;

SELECT MAX(sal) FROM emp WHERE deptno = 10;

GROUP BY

SELECT deptno, MAX(sal) FROM emp; --에러:deptno는 행이 12개인데 MAX(sal)은 행이 1개기 때문에 Group by를 써야댐

GROUP BY : SELECT절에 집합함수 적용시 개별 컬럼을 지정할 수 없음. 개별 컬럼을 지정할 경우에는 반드시 GROUP BY 절에 지정된 컬럼만 가능.

SELECT deptno, MAX(sal) FROM emp 
GROUP BY deptno ORDER BY deptno; --그룹바이를 쓰고 오더바이를 쓴다.   

부서별로 사원수 구하기
SELECT deptno, COUNT(empno) FROM emp
GROUP BY deptno ORDER BY deptno;

다중 열에서 GROUP BY 절 사용하기
SELECT deptno,job,SUM(sal) FROM emp
GROUP BY deptno,job ORDER BY deptno;

HAVING

그룹함수를 사용한 잘못된 Query[질의어라는 뜻 = 물어본다]
[오류발생]
SELECT deptno,AVG(sal) FROM emp
WHERE AVG(sal) >=2000 --WHERE에 그룹함수를 이용해서 조건을 체크하면 오류발생
GROUP BY deptno;
--WHERE절은 일반 컬럼을 쓸 때만 가능. 그룹 함수를 써서 값을 한정할 때는 WHERE을 사용할 수 없다.

HAVING : 그룹함수를 이용해서 조건 체크할 때 사용 
              알리아스 사용X
              --GROUP BY 와 같은 그룹 함수를 쓸 때 그룹함수 뒤에 쓴다.
[정상구문] 
SELECT deptno,AVG(sal) FROM emp
GROUP BY deptno
HAVING AVG(sal) >=2000;

부서별로 최대급여를 구하는데 3000을 초과한 최대급여 구하기
SELECT deptno, MAX(sal) FROM emp
GROUP BY deptno
HAVING MAX(sal) >3000;

그룹함수 중첩
SELECT MAX(AVG(sal)) FROM emp
GROUP BY deptno;

분기별로 입사한 사원의 수
SELECT TO_CHAR(hiredate,'Q') quarter, COUNT(empno) FROM emp
GROUP BY TO_CHAR(hiredate,'Q') ORDER BY quarter;

실습문제

[실습문제]
1.모든사원의 급여 최고액, 최저액,총액 및 평균액을 포시하시오. 
열 레이블을 각각 maximum,minimum,sum 및 average로 지정하고
결과를 정수로 반올림하고 세자리 단위로 ,를 명시하시오.
SELECT TO_CHAR(MAX(sal),'9,999') maximum ,
TO_CHAR(MIN(sal) ,'9,999')minimum 
,TO_CHAR(SUM(sal),'99,999') sum,
TO_CHAR(AVG(sal),'9,999') average 
FROM emp;
2.급여와 커미션을 더한 금액의 최고,최저,평균금액을 구하시오.
평균금액은 소수점 첫째자리까지 표시하시오.
SELECT MAX(sal + NVL(comm,0)), MIN(sal+NVL(comm,0)), ROUND(AVG(sal+NVL(comm,0)),1) FROM emp;
3.업무와 업무가 동일한 사원 수를 표시하시오.(업무별 사원수를 구하시오)
SELECT job,COUNT(empno) FROM emp
GROUP BY job;
4.30번 부서의 사원수를 구하시오
SELECT COUNT(*) FROM emp  WHERE deptno = 30;
4-1 부서 번호를 명시하는 경우
SELECT deptno, COUNT(*) FROM emp  WHERE deptno = 30 GROUP BY deptno;
SELECT deptno, COUNT(*) FROM emp  GROUP BY deptno HAVING deptno = 30;
5.업무별 최고 월급을 구하고 업무, 최고 월급을 출력하시오.
SELECT job , MAX(sal) FROM emp GROUP BY job; 
6.20번부서의 급여 합계를 구하고 급여 합계 금액을 출력하시오.
SELECT SUM(sal) FROM emp WHERE deptno =20;
7.부서별로 지급되는 총월급에서 금액이 9,000이상을 받는 사원들의 부서번호,총월급을 출력하시오.
SELECT deptno, SUM(sal) FROM emp GROUP BY deptno HAVING SUM(sal) >= 9000;
8.업무별로 사번이 제일 늦은 사람을 구하고 그 결과 내에서 사번이 79로 시작하는 결과만 보여주세요.
SELECT job, MAX(empno)  FROM emp GROUP BY job HAVING MAX(empno) LIKE '79%' ; --이게 더 좋은 답변
SELECT job, MAX(empno)  FROM emp WHERE empno LIKE '79%' GROUP BY job; 
9.업무별 총월급을 출력하는데 업무가 'MANAGER' 인 사원들은 제외하고 총월급이 5,000보다 많은 업무와 총월급만 출력하시오.
SELECT job, SUM(sal) FROM emp GROUP BY job HAVING (job NOT IN('MANAGER')) AND SUM(sal) >5000;
SELECT job, SUM(sal) FROM emp WHERE job <> 'MANAGER' GROUP BY job HAVING SUM(sal) >5000;
10.업무별로 사원의 수가 4명 이상인 업무와 인원수를 출력하시오.
SELECT job , COUNT(empno) FROM emp GROUP BY job HAVING COUNT(empno) >=4;