국비 4일차, 그룹함수, GROUP BY, HAVING

2021.01.08 국비교육 4일차

[TOC]

그룹함수

• 여러 행 또는 테이블 전체에 대해 함수가 적용되어 하나의 결과를 가져오는 함수를 말함

함수	설 명
AVG(DISTINCT, ALL)	NULL 값을 제외한 n개의 평균값
COUNT	NULL 값이 아닌 행의 개수
MAX(DISTINCT, ALL)	최대값
MIN(DISTINCT, ALL)	최소값
STDDEV	NULL값을 제외한 n의 표준편차
SUM(DISTINCT, ALL)	NULL값을 제외한 n의 합계
VARIANCE	NULL값을 제외한 n의 분산

• COUNT(*) l NULL을 포함한 행의 수
• COUNT(표현식)NULL을 제외한 행의 수

SELECT AVG(SAL), MAX(SAL), MIN(SAL), SUM(SAL) FROM EMP
WHERE JOB LIKE 'SAL%';
-- 월급에 대한 통계값들  1400    1600    1250    5600

SELECT SUM(DISTINCT SAL), SUM(ALL SAL), SUM(SAL) FROM EMP;
-- SUM 활용 // DISTINCT : 중복값 제외, ALL : 중복값 포함

SELECT SUM(SAL), ROUND(AVG(SAL), 2) FROM EMP;
-- 월급 합, 평균

SELECT MAX(SAL), MIN(SAL), MAX(SAL)- MIN(SAL) FROM EMP;
-- 월급 최댓값, 최솟값, 최댓값, 최솟값 차이 // 5000    800    4200

SELECT MIN(HIREDATE), MAX(HIREDATE) FROM EMP;
-- 날짜에도 적용이 가능하다. 80/12/17    82/01/23

SELECT COUNT(COMM) FROM EMP;
-- COUNT는 널값을 제외한다, 카디널리티 = 12 but, 출력값 4 --> NULL값이 8개다

SELECT COUNT(DISTINCT MGR) FROM EMP;
SELECT DISTINCT MGR FROM EMP;
-- COUNT(MGR) : 11 / COUNT DISTINCT MGR : 5 / DISTINCT MGR : 5명 + NULL값 1개

SELECT COUNT(*), COUNT(COMM), AVG(NVL(COMM, 0)), COUNT(DEPTNO) FROM EMP;
--EMP 테이블에 등록되어 있는 인원수, 보너스에 NULL이 아닌 인원수, 보너스의 평균, 등록되어 있는 부서의 수를 구하여 출력

--중요한 점은 보너스의 평균에서 그냥 AVG를 사용하면 전체인원수로 나누는게 아닌 값을 가지고 있는 개수로 나누기 때문에 결과값이 달라져서 NVL로 널 값을 없애줘야함

GROUP BY

SELECT DEPTNO, COUNT(*), ROUND(AVG(SAL)), MIN(SAL), MAX(SAL), SUM(SAL)
FROM EMP WHERE DEPTNO = 10 
GROUP BY DEPTNO ORDER BY 1;

-- 10번부서의 통계 정보

SELECT DEPTNO, JOB, COUNT(*), AVG(SAL), SUM(SAL)
FROM EMP
GROUP BY DEPTNO, JOB
ORDER BY DEPTNO;
-- 다중 GROUPING

SELECT DEPTNO, MAX(SAL), MIN(SAL) FROM EMP
GROUP BY DEPTNO;
-- 부서별 최대 월급, 최소월급, 구하고 부서번호로 정렬

SELECT TO_CHAR(HIREDATE, 'YYYY"년"') 입사년도, COUNT(*) 인원수 FROM EMP
GROUP BY TO_CHAR(HIREDATE, 'YYYY"년"');
--입사년도 별로 입사한 사람의 인원수를 출력
SELECT TO_CHAR(HIREDATE, 'YYYY"년"') 년,TO_CHAR(HIREDATE, 'MM"월"') 월, SUM(SAL) 월급합
FROM EMP
GROUP BY TO_CHAR(HIREDATE, 'YYYY"년"'), TO_CHAR(HIREDATE, 'MM"월"')
ORDER BY SUM(SAL) DESC;
--입사년도별, 어ㅜㄹ별로 입사한 사람의 월급의합계, 월급의 합계가 높은순으로 정렬

HAVING

문법 순서

-> FROM -WHERE - GROUP BY - HAVING - SELECT - ORDER BY

SELECT DEPTNO, SUM(SAL)
FROM EMP
WHERE SAL > 800
GROUP BY DEPTNO
HAVING SUM(SAL) > 8000;
-- 월급 합계가 8천이 넘어가는 부서 출력

SELECT DEPTNO 부서번호, COUNT(*)인원수 FROM EMP
GROUP BY DEPTNO
HAVING COUNT(*) >=4;
-- 부서별 인원수가 4명 이상인 부서의 부서번호와 인원수

SELECT DEPTNO 부서번호, JOB 직업명, COUNT(*)인원수 FROM EMP
GROUP BY DEPTNO, JOB;
-- 부서별 직업별 인원수

SELECT DEPTNO 부서번호, JOB 직업명, COUNT(*)인원수 FROM EMP
GROUP BY DEPTNO, JOB
HAVING JOB ='CLERK'
ORDER BY DEPTNO;
--각부서에서 직업이 CLERK인 사원 인원수 : 부서번호, 직업, 인원수

SELECT DEPTNO 부서번호, ROUND(AVG(SAL),2 ) "평균 급여", SUM(SAL) 합
FROM EMP
GROUP BY DEPTNO
HAVING MAX(SAL) >= 2900;
-- EMP 테이블에서 최대 급여가 2900이상이 있는 부서에 대해서 부서번호, 평균 급여, 급여의 합을 구하여 출력

SELECT JOB, SUM(SAL) FROM EMP
WHERE JOB NOT LIKE 'SAL%'
GROUP BY JOB
HAVING SUM(SAL) > 5000;

SELECT JOB, SAL FROM EMP;
-- EMP테이블에서 전체 월급이 5000을 초과하는 각 업무에 대해서 업무와 월급 합계를 출력하여라, 단 판매원은 제외하고 월 급여 합계로 정렬

SELECT TO_CHAR(HIREDATE, 'YYYY') 년, TO_CHAR(HIREDATE, 'MM') 월,SUM(SAL) "월급 합"
FROM EMP
GROUP BY TO_CHAR(HIREDATE,'YYYY'), TO_CHAR(HIREDATE, 'MM')
ORDER BY 1;
-- 사원테이블로부터 년도별, 월별, 급여합계를 출력

SELECT JOB,
      SUM( CASE JOB WHEN 'CLERK' THEN 1 ELSE 0 END) "CLERK",
      SUM( CASE JOB WHEN 'SALESMAN' THEN 1 ELSE 0 END) "SALESMAN",
      SUM( CASE JOB WHEN 'MANAGER' THEN 1 ELSE 0 END) "MANAGER",
      SUM( CASE JOB WHEN 'ANALYST' THEN 1 ELSE 0 END) "ANALYST",
      SUM( CASE JOB WHEN 'PRESIDENT' THEN 1 ELSE 0 END) "PRESIDENT",
COUNT(*)
FROM EMP
GROUP BY JOB;
-- 직업별 인원수 조사 --> 이렇게 하는의미가 뭐지?
-->
SELECT ENAME,
      CASE JOB WHEN 'CLERK' THEN 1 ELSE 0 END "CLERK",
      CASE JOB WHEN 'SALESMAN' THEN 1 ELSE 0 END "SALESMAN",
      CASE JOB WHEN 'MANAGER' THEN 1 ELSE 0 END "MANAGER",
      CASE JOB WHEN 'ANALYST' THEN 1 ELSE 0 END "ANALYST",
      CASE JOB WHEN 'PRESIDENT' THEN 1 ELSE 0 END "PRESIDENT"
      FROM EMP;
-- 이거 보고 생각해

SELECT JOB,
      SUM( CASE WHEN TO_CHAR(HIREDATE, 'YYYY') = '1980' THEN 1 ELSE 0 END) "1980", 
      SUM( CASE WHEN TO_CHAR(HIREDATE, 'YYYY') = '1981' THEN 1 ELSE 0 END) "1981",
      SUM( CASE WHEN TO_CHAR(HIREDATE, 'YYYY') = '1982' THEN 1 ELSE 0 END) "1982",     
COUNT(*) "직업별 총 인원수"
FROM EMP
GROUP BY JOB;

--연도별 입사한 직원의 직업

SELECT JOB,
        SUM(DECODE(TO_CHAR(HIREDATE, 'YYYY'), '1980', 1, 0)) "1980",
        SUM(DECODE(TO_CHAR(HIREDATE, 'YYYY'), '1981', 1, 0))"1981",
        SUM(DECODE(TO_CHAR(HIREDATE, 'YYYY'), '1982', 1, 0))"1982",
COUNT(*) "직업별 총 인원수"
FROM EMP
GROUP BY JOB;
-- 위와 같은 출력값