오라클[sql] 그룹함수 / GROUP BY / HAVING

그룹함수

하나의 테이블에서 하나 이상의 행을 그룹으로 묶어서 하나의 결과로 나타내는 함수

- sum(), avg(), max(), min()

- count() : 행의 갯수

 

sum(),  avg(), max(), min()

sal 14개의 행이지만 sum 그룹함수를 통해서 14개의 행에 대한 값을 다 더하여 하나의 행으로 출력
SELECT sum(sal) from emp;
SELECT sal from emp;

 

SELECT sum(sal), avg(sal), max(sal), min(sal) FROM emp;

 

count( )

집계함수 : count( ) 행의 갯수
SELECT COUNT(*) FROM EMPLOYEES;

▶ 중복처리를 한 갯수 : DISTINCT
▶ 부서의 갯수
SELECT COUNT (DISTINCT deptno) FROM emp;
▶ 직업 갯수
SELECT COUNT (DISTINCT job) FROM emp;

 

그룹함수에서  null

▶ null : 그룹함수에서 어떻게 처리하는지 확인
SELECT sal, comm, sal+comm FROM emp;
SELECT sum(comm) FROM emp;    -- NULL 을 제외하고 계산
SELECT min(comm), max(comm) FROM emp; -- NULL 을 제외하고 계산
SELECT count(comm) FROM emp;  -- NULL 을 제외하고 계산

 

▶ 데이터의 행의 갯수 : 기본키로 작성된 컬럼은 갯수를 세는데 특화 ( null 없고, 중복 없고)
    기본키로 작성되지 않는 컬럼에서 갯수를 셀때는 주의!! null 여부 중복 여부
SELECT COUNT(*) FROM emp;          -- 14
SELECT COUNT(empno) FROM emp;  -- 14 (empno가 pk)
SELECT COUNT(job) FROM emp;      -- 14개는 중복이 있을 수도 있고 null 이 있을 수도 있다 (job은 pk 아님)

 

에러 : ORA-00937

▶ ORA-00937: 단일 그룹의 그룹 함수가 아닙니다
SELECT ename, sum(sal) FROM emp;    -- 에러
-- ename : 14개의 데이터
-- sum(sal) : 1개의 데이터
-- 표현하고자하는 컬럼들의 행의 수가 달라서 발생하는 문제

▶ 문제해결방법 : 14개의 행을 표현하는 ename 단순컬럼도 그룹화를 하자! 
-- GROUP BY

 

GROUP BY

▶ GROUP BY : 컬럼들을 그룹화 시켜주는 키워드

SELECT deptno FROM emp
GROUP BY deptno;

-- 특정 컬럼값을 기준으로 그룹화 해달라는 뜻

SELECT job FROM emp
GROUP BY job;
-- Group by + 그룹함수를 같이 사용 : 그룹화 된 컬럼으로 그룹함수가 진행

 

SELECT deptno, avg(sal)

FROM emp

GROUP BY deptno;

 

GROUP BY 에러

▶ SELECT deptno, avg(sal) FROM emp;
-- 그룹함수를 사용할 때는 그룹함수를 적용하지 않은 단순컬럼은 사용불가능
-- GROUP BY 사용하면 단순컬럼이 그룹화가 되기 때문에 그룹함수를 사용할 수 있음!
-- 그룹함수와 단순컬름을 같이 쓰려면 GROUP BY 가 필수다

▶SELECT deptno, sum(sal), avg(sal), count(sal) FROM emp GROUP BY deptno ;

▶ SELECT deptno, sal FROM emp GROUP BY deptno; -- GROUP BY 표현식이 아닙니다.
-- GROUP BY 특정컬럼을 기준으로 그룹화 해주는 명령어
-- GROUP BY 뒤에 작성되지 않은 컬럼을 SELECT 문에 작성하지 못합니다.

▶SELECT deptno, job, count(*) FROM emp GROUP BY deptno, job;

 

HAVING

▶ HAVING : 그룹화 된 결과에 대한 조건을 주는 키워드

 

SELECT  >  WHERE  >  GROUP BY  >  HAVING  >  ORDER BY

▶ WHERE + GROUP BY

▶ WHERE 에 의해서 나온 결과를 대상으로 GROUP BY 그룹화 진행

▶ SELECT : 전체데이터

▶ WHERE : 전체 데이터 중에 WHERE 조건에 맞는 데이터

▶ GROUP BY : 전체 데이터 중에 WHERE 조건에 맞는 데이터를 그룹화

-- GROUP BY 절을 통해서 생성된 결과에서 "조건" 부합된 결과만 보고자 할 떄

▶ HAVING : 그룹화 된 결과에 대한 조건을 주는 키워드

 

▶ 평균이 2000이상인 직업군만 보고싶을 때

SELECT job, sum(sal), avg(sal), max(sal), min(sal)

FROM emp

GROUP BY job

HAVING avg(sal) >=2000;

 

WHERE  / HAVING  차이

▶ WHERE

-- 1. 조건을 사용하여 결과를 제한

-- 2. 단순 컬럼

-- 3. 테이블에서 특정 조건에 부합된 자료만 검색하는데 사용

 

▶HAVING

-- 1. 그룹의 결과를 제한

-- 2. 그룹함수

-- 3. 그룹함수를 적용해서 나온 결과 중에 원하는 조건에 부합되는 자료만 산출할때 사용

 

▶ 부서의 최대값, 최소값 + 최대급여가 2900원 이상인 부서만 출력

SELECT deptno, max(sal), min(sal)

FROM emp

GROUP BY deptno

HAVING max(sal) >= 2900;

 

연습문제

▶ 소속 부서별 급여 총액 평균급여

▶ 소속 부서별 최대급여 최소급여

SELECT deptno

     , sum(sal)

     , ROUND(avg(sal),2)

     , max(sal)

     , min(sal)

FROM emp

GROUP BY deptno;

 

▶부서별 사원수와 커미션을 받는 사원의 수

SELECT deptno

     , count(*)

     , count(comm) -- NULL : 포함시키지 않는 값, 0도 comm에 포함되어 있는 문제

FROM emp

GROUP BY deptno;

 

▶업무가 SALESMAN 사람들의 급여 합산, 평균급여(십단위로 나오게끔) 최소급여 최대급여

SELECT job, sum(sal), avg(sal), max(sal), min(sal)

FROM emp

WHERE job = 'SALESMAN'

GROUP BY job;

 

▶ 부서별로 인원수, 평균급여, 최저급여, 최고급여, 급여의 합을 구하기

SELECT deptno, count(deptno), avg(sal), min(sal), max(sal), sum(sal)

FROM emp

GROUP BY deptno;

 

▶ 부서별로 인원수, 평균급여, 최저급여, 최고급여, 급여의 합을 구하기 ( 부서별 급여의 합이 높은 순으로   

SELECT deptno, count(deptno), ROUND(avg(sal)), min(sal), max(sal), sum(sal)

FROM emp

GROUP BY deptno

ORDER BY sum(sal) desc;

 

▶ 부서별 업무별 그룹하여 부서번호, 업무, 인원수, 급여의 평균, 급여의 합을 구하기

SELECT deptno, job, count(deptno), avg(sal), sum(sal)

FROM emp

GROUP BY deptno, job

ORDER BY deptno;

 

▶ 최대 급여가 2900 이상인 부서에 대해 부서번호, 평균 급여, 급여의 합을 출력

SELECT deptno, avg(sal), sum(sal)

FROM emp

GROUP BY deptno

HAVING max(sal) >= 2900;

 

▶ 업무별 급여의 평균이 3000이상인  업무에 대해 업무명, 평균 급여, 급여의 합을 출력

SELECT job, avg(sal), sum(sal)

FROM emp

GROUP BY job

HAVING avg(sal) >=3000;

 

▶ 전체 합계 급여가 5000를 초과하는 각 업무에 대해서 업무와 급여 합계를 출력

    단, SALESMAN은 제외하고 급여 합계가 높은 순으로 정렬

SELECT job, sum(sal)

FROM emp

WHERE job NOT IN ('SALESMAN')

GROUP BY job

HAVING sum(sal) > 5000

ORDER BY sum(sal) desc;

▶ 업무별 최고 급여와 최소 급여의 차이를 구하라 

SELECT job, max(sal), min(sal), max(sal)-min(sal) AS "급여차이"

FROM emp 

GROUP BY job;

 

▶ 부서 인원이 4명 보다 많은 부서의 부서번호, 인원수, 급여의 합을 출력

SELECT deptno, count(deptno), sum(sal)

FROM emp

GROUP BY deptno

HAVING count(deptno) > 4;