[SQL] GROUP BY, HAVING절

SELECT [DISTINCT] 칼럼명 [ALIAS명]
FROM 테이블명
[WHERE 조건식]
[GROUP BY 칼럼이나 표현식]
[HAVING 그룹조건식];

SELECT POSITION 포지션, AVG(HEIGHT) 평균키
FROM PLAYER;
-->>ERROR : 단일 그룹의 집계함수가 아니다라고 나옴


SELECT POSITION 포지션, AVG(HEIGHT) 평균키
FROM PLAYER
GROUP BY POSITION 포지션;
-->>ERROR SQL명령어가 올바르게 종료되지 않음-->그룹바이에 별명ALIAS쓸 수 없음

--포지션별 최대키, 최소키, 평균키 출력
SELECT POSITION 포지션, MAX(HEIGHT), MIN(HEIGHT), AVG(HEIGHT)
FROM PLAYER
GROUP BY PISITION;

SELECT POSITION 포지션, ROUND(AVG(HEIGHT),2) 평균키
FROM PLAYER
WHERE AVG(HIGHT) >=180
GROUP BY POSITION
--3행오류 : 집계함수가 허가되지 않음. 그룹바이로 소그룹화 되기전에 집계함수를 사용했기때문.

SELECT POSITION, ROUND(AVG(HEIGHT),2)
FROM PLAYER
GROUP BY POSITION
HAVING AVG(HEIGNT)>=180;

--이렇게써야함
--예제)
--최대 키가 190CM이상인 선수를 가지고 포지션별 평균 키를 출력
SELECT POSICITON AVG(HEIGHT)
FROM PLAYER
GROUP BY POSITION
HAVING MAX(HEIGHT) >=190;

--STEPT1
--개별입사정보에서 월별데이터 추출
SELECT ENAME, DEPOTNO, EXTRACT(MONTH FROM HIREDATE) MONTH, SAL
FROM EMP;

--STEPT2 월별데이터 구분
SELECT ENAME, DEPTNO,
  CASE MONTH WHEN 1 THEN SAL END M01, CASE MONTH WHEN 2 THEN SAL END M02,
  CASE MONTH WHEN 3 THEN SAL END M03, CASE MONTH WHEN 4 THEN SAL END M04,
  CASE MONTH WHEN 5 THEN SAL END M05, CASE MONTH WHEN 6 THEN SAL END M06,
  CASE MONTH WHEN 7 THEN SAL END M07, CASE MONTH WHEN 8 THEN SAL END M08,
  CASE MONTH WHEN 9 THEN SAL END M09, CASE MONTH WHEN 10 THEN SAL END M10,
  CASE MONTH WHEN 11 THEN SAL END M11, CASE MONTH WHEN 12 THEN SAL END M12
FROM (SELECT ENAME, DEPTNO, EXTRACT(MONTH FROM HIREDATE) MONTH, SAL
      FROM EMP);
      
--STEPT3 2의구분을 부서별로 그룹화
SELECT DEPTNO,
  AVG(CASE MONTH WHEN 1 THEN SAL END) M01, AVG(CASE MONTH WHEN 2 THEN SAL END) M02,
  AVG(CASE MONTH WHEN 3 THEN SAL END) M03, AVG(CASE MONTH WHEN 4 THEN SAL END) M04,
  AVG(CASE MONTH WHEN 5 THEN SAL END) M05, AVG(CASE MONTH WHEN 6 THEN SAL END) M06,
  AVG(CASE MONTH WHEN 7 THEN SAL END) M07, AVG(CASE MONTH WHEN 8 THEN SAL END) M08,
  AVG(CASE MONTH WHEN 9 THEN SAL END) M09, AVG(CASE MONTH WHEN 10 THEN SAL END) M10,
  AVG(CASE MONTH WHEN 11 THEN SAL END) M11, AVG(CASE MONTH WHEN 12 THEN SAL END) M12
FROM (SELECT ENAME, DEPTNO, EXTRACT(MONTH FROM HIREDATE) MONTH, SAL
      FROM EMP)
GROUP BY DEPTNO;