SQL그룹함수 - 그룹함수와 HAVING

1. 그룹함수와 HAVING 절

HAVING절은 해석상 WHERE절과 동일.
단, 조건 내용에 그룹 함수를 포함하는 것만을 포함한다.
일반 조건은 WHERE절에 기술하지만 그룹 함수를 포함한 조건은 HAVING 절에 기술한다.

SELECT [DISTINCT | ALL] 컬럼 OR 그룹함수, ...
FROM 테이블
WHERE 조건
GROUP BY 그룹대상
HAVING <그룹 함수 포함 조건>
ORDER BY 정렬대상

HAVING : 조건중에 그룹 함수를 포함하는 조건을 기술.

• 그룹을 필터링하는 2차 필터링.
• ORACLE에서는 HAVING절과 GROUP BY의 순서가 정해져있진 않지만, 다른 DBMS에서는 GROUP BY가 HAVING절보다 앞에 작성되어야 한다.
  논리적으로도 HAVING은 그룹된 결과에 대한 조건이므로 가능한 GROUP BY 절 뒤에 기술하는것이 좋다.
• GROUP BY절에 작성되지 않은 일반컬럼은 HAVING절에 올 수 없다. (SYNTAX오류 발생)

---

EXERCISE 1

부서별 급여 평균이 3천 달러 미만인 부서의 부서번호와 평균 급여 검색

SELECT DNO, AVG(SAL)
FROM EMP
GROUP BY DNO
-- ▲ 데이터를 리스트업 하는 쿼리문
HAVING AVG(SAL) < 3000;
-- ▲ 리스트업 된 데이터에 조건 추가 >> HAVING

INLINE QUERY 로 변경

SELECT *
FROM (
      SELECT DNO, AVG(SAL) AVG_SAL
      FROM EMP
      GROUP BY DNO
      )
WHERE AVG_SAL < 3000 ;

EXERCISE 2

HAVING절의 다양한 사용법

--오류 쿼리
SELECT DNO, COUNT(*)
FROM EMP
GROUP BY DNO
HAVING JOB != '개발'

위 쿼리는 오류가 발생한다.
HAVING은 이미 리스트업 된 데이터 내에서 조건을 추가하는 절로,
GROUP BY 절에서 JOB의 데이터는 리스트업되지 않은 데이터이기 때문이다.
HAVING절 조건에 해당되는 컬럼들은 GROUP BY절에 이미 사용된 컬럼들만을 사용할 수 있다.

 SELECT DNO, COUNT(*)
 FROM EMP
 WHERE JOB != '개발'
 GROUP BY DNO ;

오류 쿼리는 위와 같이 수정할 수 있다.
일반조건은 WHERE절에 사용하는것이 좋다

EXERCISE 3

SELECT DNO, AVG(SAL)
FROM EMP
GROUP BY DNO
HAVING AVG(SAL) = (SELECT MAX(AVG(SAL)) 
                    FROM EMP 
                GROUP BY DNO);

INLINE QUERY로 변경

SELECT MAX(AVG_SAL)
FROM (
    SELECT DNO, AVG(SAL) AVG_SAL
    FROM EMP
    GROUP BY DNO
    )
;

단, INLINE QUERY 를 사용하면 DNO(부서정보)가 사라지므로 데이터를 식별하기가 어려워지므로 유의한다.

---

실습

1. 화학과를 제외하고 학과별로 학생들의 평점 평균 검색

SELECT MAJOR, ROUND(AVG(AVR),2) 평점평균
FROM STUDENT
WHERE MAJOR != '화학'
GROUP BY MAJOR;

2. 화학과를 제외한 각 학과별 평균 평점중에 평점이 2.0 이상인 정보 검색

SELECT MAJOR, ROUND(AVG(AVR),2) 평점평균
FROM STUDENT
WHERE MAJOR != '화학'
GROUP BY MAJOR
HAVING AVG(AVR) >= 2.0;

3. 기말고사 평균이 60점 이상인 학생의 정보를 검색 (학번과 기말고사 평균)

SELECT SNO 학번, ROUND(AVG(RESULT),2) 기말고사_평균
FROM SCORE
GROUP BY SNO
HAVING AVG(RESULT) >= 60
ORDER BY SNO;

4. 강의 학점수가 3학점 이상인 교수의 정보 검색 (교수번호, 교수명, 담당 학점 수)

SELECT P.PNO, P.PNAME, SUM(C.ST_NUM)
FROM PROFESSOR P, COURSE C
WHERE P.PNO = C.PNO
GROUP BY P.PNO, P.PNAME
HAVING SUM(C.ST_NUM) >= 3;

5. 기말고사 성적이 핵 화학과목보다 우수한 과목의 과목명과 담당 교수명을 검색

SELECT A.CNO
      ,B.CNAME
      ,C.PNAME
      ,ROUND(AVG(A.RESULT),2) 기말고사_성적_평균
FROM SCORE A, COURSE B, PROFESSOR C
WHERE A.CNO = B.CNO
  AND B.PNO = C.PNO
GROUP BY A.CNO, B.CNAME, C.PNAME
HAVING AVG(RESULT) > (SELECT AVG(RESULT)
                        FROM SCORE A, COURSE B 
                       WHERE A.CNO = B.CNO
                         AND B.CNAME = '핵화학')
ORDER BY A.CNO;

6. 근무중인 직원이 4명 이상인 부서 검색

SELECT E.DNO 부서번호
      ,(SELECT DNAME FROM DEPT D WHERE E.DNO = D.DNO) 부서명
      ,COUNT(*) 인원수
FROM EMP E
GROUP BY DNO
HAVING COUNT(*) >= 4;