[Oracle] GROUP BY 절

    < GROUP BY 절 >

    그룹기준을 제시 할 수 있는 구문 (해당 그룹기준별로 여러 그룹을 묶을 수 있음)
    여러개의 값들을 하나의 그룹으로 묶어서 처리할 목적으로 사용

---

SELECT SUM(SALARY)
FROM EMPLOYEE; --> 전체 사원을 하나의 그룹으로 묶어서 총합을 구한 결과

 

-- 각 부서별 총 급여 합

SELECT DEPT_CODE, SUM(SALARY)
FROM EMPLOYEE
GROUP BY DEPT_CODE;

 

-- 각 부서별 사원수

SELECT DEPT_CODE, COUNT(*), SUM(SALARY)
FROM EMPLOYEE
GROUP BY DEPT_CODE;

 

-- 실행순서

SELECT DEPT_CODE, SUM(SALARY)   -- 3
FROM EMPLOYEE   -- 1
GROUP BY DEPT_CODE  -- 2
ORDER BY DEPT_CODE; -- 4

 

-- 각 직급별 총 사원수, 급여합

SELECT JOB_CODE, COUNT(*), SUM(SALARY)
FROM EMPLOYEE
GROUP BY JOB_CODE;

 

-- 각 직급별 총 사원수, 보너스를 받는 사원수, 급여합, 평균급여, 최저급여, 최대급여 별칭 지어서 ! + 직급명 정렬 오름차순 정렬

SELECT JOB_CODE, COUNT(*) AS "총사원수", COUNT(BONUS) AS "보너스를 받는 사원수",
SUM(SALARY)AS "급여합", FLOOR(AVG(SALARY)) AS "평균급여",
MIN(SALARY) AS "최저급여", MAX(SALARY) AS "최대급여"
FROM EMPLOYEE --1
GROUP BY JOB_CODE --2 
ORDER BY 1;

 

-- 부서별 총 사원수, 보너스를 받는 사원수, 급여합, 평균급여, 최저급여, 최대급여 별칭 지어서 ! + 직급명 정렬 오름차순 정렬

SELECT DEPT_CODE, COUNT(*) AS "총사원수", COUNT(BONUS) AS "보너스를 받는 사원수",
SUM(SALARY)AS "급여합", FLOOR(AVG(SALARY)) AS "평균급여",
MIN(SALARY) AS "최저급여", MAX(SALARY) AS "최대급여"
FROM EMPLOYEE --1
GROUP BY DEPT_CODE --2 
ORDER BY 1;

 

-- GROUP BY 절에 함수식 기술 가능

SELECT DECODE(SUBSTR(EMP_NO, 8, 1), '1', '남', '2', '여'), COUNT(*)
FROM EMPLOYEE
GROUP BY SUBSTR(EMP_NO, 8, 1);

 

-- GROUP BY 절에 여러칼럼 기술 가능!

SELECT DEPT_CODE, JOB_CODE, COUNT(*), SUM(SALARY)
FROM EMPLOYEE
GROUP BY DEPT_CODE, JOB_CODE
ORDER BY DEPT_CODE;