GROUP BY 절
GROUP BY절 vs HAVING절
부서별 급여 합계
SELECT DEPARTMENT_ID, SUM(salary)
FROM EMPLOYEES
GROUP BY DEPARTMENT_ID;
| DEPARTMENT_ID | SUM(SALARY) |
|---|
| 100 | 51608 |
| 30 | 24900 |
| (NULL) | 7000 |
부서별 사원수와 평균 급여를 구해보자
SELECT DEPARTMENT_ID, SUM(salary), COUNT(salary), AVG(salary)
FROM EMPLOYEES
GROUP BY DEPARTMENT_ID;
| DEPARTMENT_ID | SUM(SALARY) | COUNT(SALARY) | AVG(SALARY) |
|---|
| 100 | 51608 | 6 | 8601.3333... |
| 30 | 24900 | 6 | 4150 |
| (NULL) | 7000 | 1 | 7000 |
| 20 | 19000 | 2 | 9500 |
- GROUPT BY 절의 컬럼과, SELECT 절의 컬럼은 동일해야 함.
부서별, 직급별 사원수와 평균 급여를 구하고 부서 ID기준으로 오름차순으로 정렬
SELECT DEPARTMENT_ID, JOB_ID, SUM(salary), COUNT(salary), AVG(salary)
FROM EMPLOYEES
GROUP BY DEPARTMENT_ID, JOB_ID;
ORDER BY DEPARTMENT_ID, JOB_ID;
| DEPARTMENT_ID | JOB_ID | SUM(SALARY) | COUNT(SALARY) | AVG(SALARY) |
|---|
| 10 | AD_ASST | 4400 | 1 | ... |
| 20 | MK_MAN | 13000 | 1 | ... |
| 20 | MK_RED | 6000 | 1 | ... |
| 30 | PU_CLERK | 13900 | 5 | ... |
- GROUP BY 절의 컬럼과, SELECT 절의 컬럼은 동일해야 함.
TO_CHAR() 함수를 이용해서 출력값 형태 변경하기
SELECT DEPARTMENT_ID, JOB_ID,
TO_CHAR(SUM(salary), '999,999') 총급여,
TO_CHAR(AVG(salary), '999,999') 평균급여
FROM EMPLOYEES
GROUP BY DEPARTMENT_ID, JOB_ID;
ORDER BY DEPARTMENT_ID,JOB_ID;
| DEPARTMENT_ID | JOB_ID | 총급여 | 평균급여 |
|---|
| 10 | AD_ASST | 4,400 | 4,400 |
| 20 | MK_MAN | 13,000 | 13,000 |
| 20 | MK_REP | 6,000 | 6,000 |
| 30 | PU_CLERK | 13,900 | 2,780 |
| 30 | PU_MAN | 11,000 | 11,000 |
| 40 | HR_REP | 6,500 | 6,500 |
| 50 | SH_CLERK | 64,400 | 3,215 |
HAVING 절
현재 부서별 사원수가 10명 이상인 경우
SELECT DEPARTMENT_ID, COUNT(*)
FROM EMPLOYEES
WHERE DEPARTMENT_ID IS NOT NULL
GROUP BY DEPARTMENT_ID
HAVING COUNT(*) >= 10;
| DEPARTMENT_ID | COUNT(*) |
|---|
| 50 | 45 |
| 80 | 34 |
ROLLUP()
SELECT L.CITY, D.DEPARMENT_NAME, E.JOB_ID,
COUNT(*) 사원수, SUM(E.SALARY) 총급여
FROM EMPLOYEES E, DEPARTMENTS D, LOCATIONS L
WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID
AND D.LOCATION_ID = L.LOCATION_ID
GROUP BY ROLLUP(L.CITY, D.DEPARTMENT_NAME, E.JOB_ID)
ORDER BY L.CITY, D.DEPARTMENT_NAME, E.JOB_ID;
| CITY | DEPARTMENT_NAME | JOB_ID | 사원수 | 총급여 |
|---|
| London | Human Resources | HR_REP | 1 | 6500 |
| Munich | Public Relations | PR_REP | 1 | 10000 |
| Oxford | Sales | SA_MAN | 5 | 61000 |
| Seattle | Accounting | AC_ACCOUNT | 1 | 8300 |
| South San Francisco | Shipping | SH_CLERK | 20 | 64300 |
| CITY | DEPARTMENT_NAME | JOB_ID | 사원수 | 총급여 |
|---|
| London | Human Resources | HR_REP | 1 | 6500 |
| Munich | Public Relations | PR_REP | 1 | 10000 |
| Oxford | Sales | SA_MAN | 5 | 61000 |
| Seattle | Accounting | AC_ACCOUNT | 1 | 8300 |
| South San Francisco | Shipping | SH_CLERK | 20 | 64300 |
Reference
