Oracle SQL(4)

그룹함수

• 여러 행 또는 테이블 전체에 대해 함수가 적용되어 하나의 결과를 반환하는 함수를 의미한다.
• 그룹당 하나의 결과가 주어지도록 행의 집합에 대해 연산할 경우 GROUP BY 절을 이용하여 그룹화 할 수 있고, HAVING을 이용하여 그룹에 대한 조건을 제한할 수 있다.
• SELECT, HAVING, GROUP BY 절에 사용 가능하다.

1. SUM

• NULL 값을 제외한 해당 컬럼 값들의 총합을 구하는 함수이다.
• SUM( DISTINCT | ALL | 컬럼명 )
• DISTINCT 키워드를 사용하면 중복된 값을 제외하고 총합 계산이 처리된다.
• ALL을 중복된 값을 모두 포함하여 총합 계산이 처리되며 일반적으로 생략하고 사용한다.

--           1                  2          3
SELECT SUM(DISTINCT sal), SUM(ALL sal), SUM(sal)
FROM emp;

-- 2와 3은 결과가 같음

2. AVG

• NULL 값을 제외한 해당 컬럼 값들의 평균을 구하는 함수이다.
• AVG( DISTINCT | ALL | 컬럼명 )
• DISTINCT 키워드를 사용하면 중복된 값을 제외하고 총합 계산이 처리된다.
• ALL을 중복된 값을 모두 포함하여 평균 계산이 처리되며 일반적으로 생략하고 사용한다.
• NULL 값을 포함하려면 NVL이나 NVL2를 사용해야 한다.

SELECT SUM(sal), AVG(sal)
FROM emp;

SELECT AVG(NVL(comm, 0))
FROM emp;

3. MAX, MIN

• 해당 컬럼 값들 중에서 최대값/최소값을 구하는 함수이다.
• MAX( DISTINCT | ALL | 컬럼명 ) MIN( DISTINCT | ALL | 컬럼명 )
• DISTINCT 키워드를 사용하면 중복된 값을 제외하고 총합 계산이 처리된다.
• ALL을 중복된 값을 모두 포함하여 최대값, 최소값 계산이 처리되며 일반적으로 생략하고 사용한다.
• MAX, MIN 함수는 숫자 데이터뿐만 아니라 문자 및 날짜데이터에도 사용 가능하다.

SELECT MIN(hiredate), MAX(hiredate)
FROM emp;

SELECT MAX(sal), MIN(sal)
FROM emp;

4. COUNT

• 테이블에서 조건을 만족하는 행의 개수를 반환하는 함수이다.
• COUNT( DISTINCT | ALL | 컬럼명 )
• 특정 컬럼을 지정하면 NULL 값을 제외한 해당 컬럼 값이 가지고 있는 행의 개수를 반환한다.
• DISTINCT 키워드를 사용하면 중복된 값을 제외하고 총합 계산이 처리된다.
• ALL을 중복된 값을 모두 포함하여 개수를 세며 일반적으로 생략하고 사용한다.
• COUNT(*)은 NULL값 포함해서 테이블 전체의 행의 개수를 반환한다.

SELECT COUNT(ename), COUNT(comm)
FROM emp;

GROUP BY

• 사원 테이블에서 가장 월급이 많은 사원 정보를 출력하기 위해 다음과 같은 SQL문을 실행하면 에러가 발생한다.

SELECT deptno, AVG(sal)
FROM emp;

• 이유는 그룹 함수인 MAX(sal)의 실행 결과는 하나인데, 그룹함수를 적용하지 않은 단순 컬럼인 dept의 결과는 12개이기 때문이다.
• 즉, 둘의 실행 결과가 달라서 둘을 동시에 출력할 수 없기 때문이다.
• 결론적으로 그룹함수와 단순 컬럼은 SELECT 절에서 동시 사용이 불가능하다. 하지만 GROUP BY 절을 사용하여 단순 컬럼으로 묶으면 동시 사용이 가능해진다.
  
  
• GROUP BY는 특정 컬럼값을 기준으로 그룹으로 묶을 때 사용하는 방법이다.

SELECT [단순 컬럼 ,] 그룹함수, 그룹함수2
FROM 테이블명
[WHERE 조건식]
[GROUP BY 단순 컬럼]
[ORDER BY 표현식];

-- 안되는 경우 (x)
SELECT depno, AVG(sal)
FROM emp
WHERE AVG(sal) > 1500
GROUP BY deptno;

• SELECT 절 뒤에 사용할 수 있는 단순 컬럼은 GROUP BY 뒤에 기술된 컬럼이거나 그룹함수가 적용된 컬럼만 사용 가능하다.
• WHERE 절을 사용하여 행을 그룹으로 그룹핑하기 전에 제외 시킬 수 있다.
• 그룹으로 묶은 후 행을 제외시키려면 HAVING절을 사용한다.
• GROUP BY 절 뒤에는 컬럼 별칭 및 컬럼 순서 위치 값을 사용할 수 없다.
• WHERE 절에는 그룹함수를 사용할 수 없다.
• SELECT -> WHERE -> GROUP BY -> ORDER BY 순서

-- deptno으로 그룹화 후 job으로 그룹화 
SELECT deptno, job, COUNT(*), AVG(sal), SUM(sal)
FROM emp
GROUP BY deptno, job
ORDER BY deptno, job;

-- 부서별 최대월급, 최소월급을 구하고 부서번호로 정렬
SELECT deptno, MAX(sal), MIN(sal)
FROM emp
GROUP BY deptno
ORDER BY deptno;

-- 입사년도별로 입사한 사람의 인원수를 출력, 인원수 높은순으로 출력, 컬럼명 : 입사년도, 인원수
SELECT TO_CHAR(hiredate, 'YYYY') 입사년도, COUNT(*) 인원수
FROM emp
GROUP BY TO_CHAR(hiredate, 'YYYY')
ORDER BY 2 DESC;

-- 입사년도별, 월별로 입사한 사람의 월급의 합계, 월급의 함계가 높은순으로 출력, 컬럼명 : 년, 월, 웝급합계
SELECT TO_CHAR(hiredate, 'YYYY') 년, TO_CHAR(hiredate, 'MM') 월, SUM(sal) 월급합계
FROM emp
GROUP BY TO_CHAR(hiredate, 'YYYY'), TO_CHAR(hiredate, 'MM')
ORDER BY SUM(sal) DESC;

HAVING

• GROUP BY에 의해 분류된 그룹들을 제한하기 위한 방법.

SELECT [단순 컬럼, ] 그룹함수, 그룹함수2
FROM 테이블명
[WHERE 조건식] -- 그룹화 전 1차 필터
[GROUP BY 단순 컬럼]
[HAVING 조건식] -- 그룹화 후 2차 필터
[ORDER BY 표현식];

• 그룹화 한 이후에 HAVING조건을 만족하지 않는 것들은 탈락시킨다.
• SELECT -> WHERE -> GROUP BY -> HAVING -> ORDER BY 순서

--부서변 인원수가 4명 이상인 부서의 부서번호와 인원수
SELECT deptno AS "부서번호", COUNT(*) AS "인원 수"
FROM emp
GROUP BY deptno
HAVING COUNT(*) > 4;

--부서별에 직업별 인원수 : 부서번호, 직업명, 인원수 
SELECT deptno AS "부서번호", job AS "직업명", COUNT(*) AS "인원 수"
FROM emp
GROUP BY deptno, job;

-- 각 부서에서 직업이 'CLERK'인 사원의 인원수 : 부서번호, 직업, 인원수
SELECT deptno AS "부서번호", job AS "직업명", COUNT(*) AS "인원 수"
FROM emp
GROUP BY deptno, job
HAVING job = 'CLERK';

-- EMP 테이블에서 최대 급여가 2900이상이 있는 부서에 대해서 부서번호, 평균 급여, 급여의 합을 구하여 출력하라 
SELECT deptno AS "부서번호", AVG(sal) AS "평균 급여", SUM(sal) AS "급여 총합"
FROM emp
GROUP BY deptno
HAVING MAX(sal) >= 2900;

-- EMP 테이블에서 업무 별 급여의 평균이 3000 이상인 업무에 대해서 업무명, 평균 급여, 급여의 합을 구하여 출력하라
SELECT job AS "업무명", AVG(sal) AS "평균 급여", SUM(sal) AS "급여 총합"
FROM emp
GROUP BY job
HAVING AVG(sal) >= 3000;

-- EMP 테이블에서 전체 월급이 5000을 초과하는 각 업무에 대해서 업무와 월급 합계를 출력하여라
-- 단 판매원은 제외하고 월 급여 합계로 정렬(내림차순)
SELECT job AS "업무", SUM(sal) AS "월급 합계"
FROM emp
GROUP BY job
HAVING  job != 'SALESMAN' AND SUM(sal) > 5000
ORDER BY 2 DESC;

-- EMP테이블에서 연도별, 월별 급여합계를 구하라 
SELECT TO_CHAR(hiredate, 'YYYY') AS "연도", TO_CHAR(hiredate, 'MM') AS "월", SUM(sal) AS "급여 합계"
FROM emp
GROUP BY TO_CHAR(hiredate, 'YYYY'), TO_CHAR(hiredate, 'MM')
ORDER BY 연도;

-- EMP 테이블에서 각 직업별로 인원 수와 인원 수의 총합을 도표로 구하시오
SELECT
    SUM( CASE job WHEN 'CLERK' THEN 1 ELSE 0 END) "CLERK",
    SUM( CASE job WHEN 'SALESMAN' THEN 1 ELSE 0 END) "SALESMAN",
    SUM( CASE job WHEN 'MANAGER' THEN 1 ELSE 0 END) "MANAGER",
    SUM( CASE job WHEN 'ANALYST' THEN 1 ELSE 0 END) "ANALYST",
    SUM( CASE job WHEN 'PRESIDENT' THEN 1 ELSE 0 END) "PRESIDENT",
    COUNT(*)
FROM emp
GROUP BY job;