0927 DB - GROUP BY 절 ~ HAVING절
GROUP BY절
테이블의 행에 대해서 특정 기준으로 동일한 값을 가진 행들을 그룹의 단위로 묶어 주는 옵션절
- GROUP BY 그룹기준컬럼
그룹함수를 SELECT절에서 사용하는 경우 SELECT절에는 GROUP BY절에서 사용된 컬럼을 제외하고 SELECT절에서 입력이 불가능하다.
이 때 SELECT절에 사용된 그룹의 기준 컬럼은 그룹의 이름 용도로 사용이 되며 필요 없는 경우 출력하지 않아도 상관없다.
GROUP BY절과 WHERE절이 같이 활용된 경우 WHERE절이 우선 적용되어 조건식에 맞는 행을 출력하게 되고 이후 해당 행들에 GROUP BY절을 적용하여 그룹을 생성하게 된다.
GROUP BY절에 여러 컬럼의 기준을 설정하는 경우 해당 컬럼의 값들이 모두 일치하는 행들만 그룹으로 묶어 그룹단위를 만들게 된다. 하나의 값이라도 다른 경우 개별 그룹으로 나누어 진다.
SELECT department_id, job_id, COUNT(*), SUM(salary)
FROM employees
WHERE department_id > 40
GROUP BY department_id, job_id
ORDER BY department_id;
DEPARTMENT_ID|JOB_ID |COUNT(*)|SUM(SALARY)|
-------------+----------+--------+-----------+
50|SH_CLERK | 20| 64300|
50|ST_CLERK | 20| 55700|
50|ST_MAN | 5| 36400|
60|IT_PROG | 5| 28800|
70|PR_REP | 1| 10000|
80|SA_MAN | 5| 61000|
위의 예문을 실행하는 경우 아래와 같은 에러가 발생한다.
에러가 난 이유는 COUNT 함수와 같이 그룹함수를 출력하는 경우 SELECT절에는 그룹 함수를 제외한 일반 컬럼은 GROUP BY절에서 사용된 컬럼들만 사용이 가능하나 예문의 department_id는 조건에 해당하지 않으므로 에러가 발생한다.
SELECT department_id, COUNT(last_name)
FROM employees;
SQL Error [937] [42000]: ORA-00937: 단일 그룹의 그룹 함수가 아닙니다
해결 방법 1
department_id 컬럼을 SELECT절에서 제거한다.
SELECT COUNT(last_name)
FROM employees;
COUNT(LAST_NAME)|
----------------+
107|
해결 방법 2
다음과 같이 GROUP BY절을 추가하여 department_id별 그룹을 묶어 해결한다.
SELECT department_id, COUNT(last_name)
FROM employees
GROUP BY department_id;
DEPARTMENT_ID|COUNT(LAST_NAME)|
-------------+----------------+
50| 45|
40| 1|
110| 2|
90| 3|
30| 6|
70| 1|
SELECT department_id, job_id, COUNT(last_name)
FROM employees
GROUP BY department_id;
SQL Error [979] [42000]: ORA-00979: GROUP BY 표현식이 아닙니다.
해결 1
SELECT절에서 불필요한 job_id를 제거한다.
예문에서 job_id컬럼은 GROUP BY절에서 사용되지 않은 컬럼으로 SELECT절에서는 사용이 불가능하다.
SELECT department_id, COUNT(last_name)
FROM employees
GROUP BY department_id;
DEPARTMENT_ID|COUNT(LAST_NAME)|
-------------+----------------+
50| 45|
40| 1|
110| 2|
90| 3|
30| 6|
70| 1|
해결 2
그룹 생성 시 department_id, job_id의 값을 기준으로 그룹을 생성하게 되면 SELECT절에 job_id컬럼을 사용할 수 있게 된다.
SELECT department_id, job_id, COUNT(last_name)
FROM employees
GROUP BY department_id, job_id;
DEPARTMENT_ID|JOB_ID |COUNT(LAST_NAME)|
-------------+----------+----------------+
90|AD_VP | 2|
100|FI_MGR | 1|
80|SA_REP | 29|
|SA_REP | 1|
90|AD_PRES | 1|
20|MK_REP | 1|
110|AC_MGR | 1|연습문제 풀이(p.25)
직원의 최대 salary, 최소 salary, salary의 합계, 평균을 나타내기
SELECT job_id, MAX(salary) "Maximum",
MIN(salary) "Minimum", SUM(salary) "Sum",
AVG(salary) "Average"
FROM employees
GROUP BY job_id;
JOB_ID |Maximum|Minimum|Sum |Average|
----------+-------+-------+------+-------+
AD_VP | 17000| 17000| 34000| 17000|
FI_ACCOUNT| 9000| 6900| 39600| 7920|
PU_CLERK | 3100| 2500| 13900| 2780|
SH_CLERK | 4200| 2500| 64300| 3215|
Q. employees 테이블에서 manager의 일을 하는 사원의 수를 구하는 구문을 작성. 컬럼명은 'Number of Managers'
SELECT COUNT(DISTINCT manager_id) "Number of Managers"
FROM employees;
Number of Managers|
------------------+
18|
Q. 사원의 입사일에 대해서 2007년도 월 별 입사자의 수를 구하시오.
7년도 입사자 행 → 월별(변환함수) 정보로 그룹 묶기(GROUP BY) → 그룹 함수
SELECT TO_CHAR(hire_date, 'YY/MM') "Year/Month",
COUNT(*) "Monthly Hired"
FROM employees
WHERE TO_CHAR(hire_date, 'YY') = '07'
GROUP BY TO_CHAR(hire_date, 'YY/MM')
ORDER BY "Year/Month";
Year/Month|Monthly Hired|
----------+-------------+
07/01 | 1|
07/02 | 3|
07/03 | 3|
07/04 | 1|
07/05 | 2|
07/06 | 2|
07/08 | 1|
07/10 | 1|
07/11 | 2|
07/12 | 3|
HAVING절
GROUP BY절에 의해 생성된 그룹들에 대한 조건절