오라클 함수정리 4탄 (그룹함수)

-- 집합함수 GROUP BY 절은 일반적으로 질의문으로부터 얻은 결과에 대해 GROUP BY 정에 명시한 

-- 컬럼의 값이 같을 때 그룹을 만들고, 이그룹으로 집합함수를 통해 다향한 결과를 도출한다

;

--전체사원의 평균 급여를 구하시오

SELECT TRUNC (AVG(SALARY)) 

  FROM EMPLOYEES;

--10000이상의 급여를 받는 사람들의 평균급여를 구하십시오

SELECT TRUNC(AVG(SALARY))

  FROM EMPLOYEES   

 WHERE  SALARY >= 10000;

  

--부서별 급여의 평균을 구하십시오

--IS NOT NULL

SELECT 

DEPARTMENT_ID

, AVG(SALARY)

  FROM EMPLOYEES

 WHERE DEPARTMENT_ID IS NOT NULL

 GROUP BY DEPARTMENT_ID

 ORDER BY DEPARTMENT_ID;

 

--업무별로 최대 급여와 최소 급여를 구하시오

--단, 직무는 영업부서(SA_MAN, SA_REP)로 제한합니다.

SELECT JOB_ID

      , MAX(SALARY)

      , MIN(SALARY)

  FROM EMPLOYEES

 WHERE JOB_ID IN('SA_REP','SA_MAN')

 GROUP BY JOB_ID;

 

--최대 최소값을 구해서 눈으로 확인해볼 수 있는 원데이터 파일보기 

SELECT JOB_ID

, SALARY  

  FROM EMPLOYEES

 WHERE JOB_ID IN('SA_REP','SA_MAN')

 ORDER BY JOB_ID ASC, SALARY DESC ; 

    

--연도별 입사자들의 평균 급여를 입사일, 평균 급여 순으로 출력하시오 

--위 오류를 보면 SELECT절의 HIRE_DATE 컬럼이 GROUP BY의 표현이 아니라는 오류를 나타내고 있다.

--이와 같은 오류는 GROUP BY 절을 사용할 때 SELECT절에 사용될 수 있는 컬럼이 제한적임을 나타내고 있음

--즉, GROUP BY 절에서 사용한 컬럼과 그룹함수만 사용될 수 있다.

--따라서, GROUP BY 식에서 사용한 컬럼이 TO_CHAR() 함수에 의해 값이 변경되었다면 변형된 형태로 

--SELECT 절에 사용되어야 한다.

--조건을 맞춰줘야 한다.

 SELECT TO_CHAR(HIRE_DATE,'YYYY') 

      , AVG(SALARY)

   FROM EMPLOYEES

  GROUP BY TO_CHAR(HIRE_DATE,'YYYY')

  ORDER BY TO_CHAR(HIRE_DATE,'YYYY');

  

--조건은 WHERE 절에 준다! (그룹을 묶이지 않았을 경우) 

--SELSCT

--FROM

--WHERE

--GROUP BY

--HAVING

--ORDER BY

--부서별 평균 급여를 구하시오, 단 평균 급여가 10000이상인 부서만 출력하세요

;

SELECT DEPARTMENT_ID

, ROUND(AVG(SALARY)) AS SAVG

--   ROUND로 SALARY 뒤에 있는 것들을 자른다

-- HAVING은 조건을 사용할 때의 WHERE와는 다르다 왜냐하면

-- 급여의 평균을 잡고 난 후에 보는 것과 평균급여일 때에는 결과의 차이가 다르다

-- WHERE  SALARY >= 10000;

  FROM EMPLOYEES

 GROUP BY DEPARTMENT_ID

HAVING AVG(SALARY) >= 10000;

 

--1. 인사테이블에서 부서별, 업무별로 /부서번호, 업무, 인원수, 급여의 평균,/ 급여의 합을 출력하시오

--AND는 WHERE 에서만 먹힘

;;;

SELECT

DEPARTMENT_ID

, JOB_ID

     , COUNT(LAST_NAME)

     , AVG(SALARY)

     , SUM(SALARY)

  FROM EMPLOYEES

 GROUP BY DEPARTMENT_ID,JOB_ID ;

---> 1.1  업무별, 부서별로 변경하여 출력하시오

;

SELECT

DEPARTMENT_ID

, JOB_ID

     , COUNT(LAST_NAME)

     , AVG(SALARY)

     , SUM(SALARY)

  FROM EMPLOYEES

 GROUP BY JOB_ID, DEPARTMENT_ID;

 

--2. 부서인원이 3명 이상인 부서의 인원수와 급여의 합을 출력하시오

;

SELECT

       DEPARTMENT_ID

     , COUNT(*)

     , SUM(SALARY)

  FROM EMPLOYEES

 GROUP BY DEPARTMENT_ID

 HAVING COUNT(*) >= 3;

--3.  인사테이블에서/ 전체 급여가 10000을 초과/ 하는 각 업무에 대해 업무와 급여의 합계/를 출력하시오. 

--단, /판매원은 제외하고 급여합으로 내림차순/ 하시오 

;

SELECT 

JOB_ID

    , SUM(SALARY)

  FROM EMPLOYEES

 WHERE JOB_ID NOT IN('SA_MAN','SA_REP') 

 GROUP BY JOB_ID

HAVING SUM(SALARY) > 10000  

 ORDER BY SUM(SALARY) DESC;

--IN 사용 예제

-- COUNT 안에 들어있는 (*)는 NULL 값을 무시한다. 하지만 (COMM)은 인식하지 않는다. 

;

--그룹함수 문제6 상식적으로 JOB_ID로 묵는 것을 푸는 것이다 <-

SELECT DEPARTMENT_ID

-- JOB_ID <- ERROR

, AVG(SALARY)

     , MAX(SALARY)

     , MIN(SALARY)

     , SUM(SALARY)

  FROM EMPLOYEES

 GROUP BY DEPARTMENT_ID , JOB_ID

 ;

 

SELECT DEPARTMENT_ID

, COUNT(*)

, AVG(SALARY)

     , MAX(SALARY)

     , MIN(SALARY)

     , SUM(SALARY)

  FROM EMPLOYEES

 GROUP BY DEPARTMENT_ID 

 ORDER BY SUM(SALARY) ASC

 ;

--위아래 예재의 NULL 값의 차이가 있다. 

 ;

 SELECT DEPARTMENT_ID

, COUNT(DEPARTMENT_ID)

, AVG(SALARY)

     , MAX(SALARY)

     , MIN(SALARY)

     , SUM(SALARY)

  FROM EMPLOYEES

 GROUP BY DEPARTMENT_ID 

 ORDER BY SUM(SALARY) ASC

 ;