/* 03_그룹함수.sql */

/*
# 그룹 함수
- 하나 이상의 행을 그룹으로 묶어서 결과를 나타냄.
- SELECT 문 뒤에 작성하고, 여러 그룹 함수를 쉼표로 구분하여 함께 사용할 수 있음.
  Ex) SELECT group_function( column ), .....
      FROM table_name;
- 그룹 함수는 해당 컬럼 값이 NULL 인 것 제외하고 계산.
*/

/*
 * # SUM
 * - 해당 컬럼 값들에 대한 총합을 구함.
 */

-- 급여 총액
SELECT SUM(SAL)
FROM EMP;
/*
 * # AVG
 * - 해당 컬럼에 대한 평균을 구함.
 */

-- 급여 평균
SELECT TRUNC(AVG(SAL),2) "급여평균"
FROM EMP;

/*
 * # MAX, MIN
 * - 최대, 최소값을 구함.
 */

-- 가장 높은 급여와 낮은 급여
SELECT MAX(SAL) "최고 급여", MIN(SAL) "최소 급여" FROM EMP;

/*
 * # COUNT
 * - 행의 갯수를 반환.
 */

-- 전체 사원수와 커미션 받는 사원수
SELECT COUNT(*), COUNT(COMM) FROM EMP;

-- 업무 종류
SELECT COUNT(DISTINCT(JOB)) "업무수" FROM EMP;

/*
 * # GROUP BY
 * - 어떤 컬럼값을 기준으로 그룹 함수 적용가능.
 *   Ex) SELECT 컬럼명, 그룹 함수
 *       FROM 테이블명
 *       WHERE 조건
 *       GROUP BY 컬럼명;
 */ 

-- 부서번호 그룹
SELECT DEPTNO
FROM EMP
GROUP BY DEPTNO;

-- 부서별 평균 급여
SELECT DEPTNO, AVG(SAL) FROM EMP GROUP BY DEPTNO;

-- 부서별 최대, 최소 급여
SELECT DEPTNO, MAX(SAL) "최대 급여", MIN(SAL) "최소 급여" FROM EMP GROUP BY DEPTNO;
SELECT DEPTNO, COUNT(*), COUNT(COMM) FROM EMP GROUP BY DEPTNO ORDER BY DEPTNO DESC;

/*
 * # HAVING 
 * - 그룹의 결과를 제한할 때 HAVING 절 사용.
 */

-- 부서별 평균 급여를 구하고, 부서별 평균 급여가 2000 이상인 부서만 출력.
SELECT DEPTNO "부서 번호" , TRUNC(AVG(SAL),1) "평균 급여" FROM EMP GROUP BY DEPTNO HAVING AVG(SAL) >= 2000;

/* quiz */
-- 가장 최근에 입사한 사원의 입사일과 가장 오래된 사원의 입사일을 출력.
SELECT MAX(HIREDATE) "최근 입사한 사원", MIN(HIREDATE) "가장 오래된 사원" FROM EMP;
-- 부서별 커미션을 받는 사원의 수를 출력하세요
SELECT DEPTNO, COUNT(COMM) FROM EMP GROUP BY DEPTNO;
-- SALESMAN   의 급여에 대해서 평균, 최고액, 최저액, 합계를 구하세요
SELECT JOB,AVG(SAL)"평균",MAX(SAL)"최고액",MIN(SAL)"최저액",SUM(SAL)"합계" 
FROM EMP 
GROUP BY JOB 
HAVING JOB ='SALESMAN';
-- 각 부서별로 인원수, 급여 평균, 최저 급여, 최고 급여, 급여의 합을 구하고
-- 급여의 합이 높은 순서로 출력하세요
SELECT DEPTNO,COUNT(*)"인원수",TRUNC(AVG(SAL))"평균 급여",MIN(SAL)"최저 급여",MAX(SAL)"최소 급여",SUM(SAL)"급여의 합"
FROM EMP 
GROUP BY DEPTNO 
ORDER BY SUM(SAL) DESC;
-- 직급별 급여의 평균이 3000 이상인 직급에 대해서 직급, 평균 급여, 급여의 합을 출력하세요
SELECT JOB"직급", AVG(SAL)"평균 급여", SUM(SAL)"급여의 합" 
FROM EMP 
GROUP BY JOB 
HAVING AVG(SAL)>=3000;
-- 전체 월급이 4000을 초과하는 각 업무에 대해서 업무와 월급여 합계를 출력하세요
-- 단, SALESMAN 은 제외하고 월급여 합계로 내림차순 정렬합니다
SELECT JOB, SUM(SAL)"급여의 합" 
FROM EMP 
WHERE JOB NOT LIKE '%SALESMAN%' 
GROUP BY JOB HAVING SUM(SAL)>4000 
ORDER BY SUM(SAL) DESC;