0928 DB - Having 절

GROUP BY절에 의해 생성된 그룹들에 대한 조건절

HAVING절에는 그룹 함수의 결과값과 조건값을 비교하여 조건을 설정한다.

WHERE절의 대부분의 조건연산자들을 활용할 수 있다.

 

 

 

SELECT job_id, SUM(salary) payroll
FROM employees
WHERE job_id NOT LIKE '%REP%'
GROUP BY job_id
--HAVING SUM(salary) > 13000
ORDER BY SUM(salary);





JOB_ID    |PAYROLL|

----------+-------+

AD_ASST   |   4400|

AC_ACCOUNT|   8300|

PU_MAN    |  11000|

FI_MGR    |  12008|

AC_MGR    |  12008|

MK_MAN    |  13000|

PU_CLERK  |  13900|

AD_PRES   |  24000|

IT_PROG   |  28800|

 

 

 

SELECT job_id, SUM(salary) payroll
FROM employees
WHERE job_id NOT LIKE '%REP%'
GROUP BY job_id
HAVING SUM(salary) > 13000
ORDER BY SUM(salary);



JOB_ID    |PAYROLL|

----------+-------+

PU_CLERK  |  13900|

AD_PRES   |  24000|

IT_PROG   |  28800|

AD_VP     |  34000|

ST_MAN    |  36400|

FI_ACCOUNT|  39600|

ST_CLERK  |  55700|

SA_MAN    |  61000|

SH_CLERK  |  64300|

 

연습문제 풀이 p.25

SELECT manager_id, MIN(salary)
FROM employees
WHERE NOT (manager_id IS NULL)
GROUP BY manager_id
HAVING MIN(salary) >= 6000
ORDER BY MIN(salary) DESC;




MANAGER_ID|MIN(SALARY)|

----------+-----------+

       102|       9000|

       205|       8300|

       145|       7000|

       146|       7000|

       108|       6900|

       149|       6200|

       147|       6200|

       148|       6100|

       201|       6000|

 

 

함수 끝!