Oracle_2. 함수(그룹)

 

• 함수 Function
  • 단일행 함수
  • 그룹 함수
    • SUM
    • AVG
    • MIN
    • MAX
    • COUNT

 

-------------------------------------------- <그룹 함수> -------------------------------------------

1. SUM (숫자타입칼럼)

=> 칼럼 값들의 총 합계를 반환

 

-- 전체 사원들의 총 급여 합계
SELECT SUM(SALARY)
FROM EMPLOYEE;

 

-- 부서코드가 D5인 사원들의 급여 합계
SELECT SUM(SALARY)
FROM EMPLOYEE
WHERE DEPT_CODE = 'D5';

 

-- 남자 사원들의 총 급여 합계
SELECT SUM(SALARY)
FROM EMPLOYEE
WHERE SUBSTR(EMP_NO, 8, 1) = 1;

 

 

2. AVG( 숫자타입칼럼)

=> 칼럼 값들의 평균을 반환

 

-- 사원들의 급여 평균
SELECT ROUND(AVG(SALARY))
FROM EMPLOYEE;

 

 

3. MIN(ANY타입 칼럼)

=> 해당 칼럼 값들 중에서 가장 작은 값을 반환

 

-- 전체 사원들 중 최저급여, 가장 작은이름, 가장작은 이메일값, 가장 과거에 입사한 날짜
SELECT MIN(SALARY), SALARY
FROM EMPLOYEE;
-- 반환해줘야 하는 결과값의 갯수가 다르기 때문에 에러발생함


SELECT MIN(SALARY), MIN(EMP_NAME), MIN(EMAIL), MIN(HIRE_DATE)
FROM EMPLOYEE;

 

 

4. MAX(ANY타입 칼럼)

=> 해당 칼럼 값들 중에서 가장 큰 값을 반환

 

SELECT MAX(SALARY), MAX(EMP_NAME), MAX(EMAIL), MAX(HIRE_DATE)
FROM EMPLOYEE;

 

 

5. COUNT (*/칼럼명/DISTINCT 칼럼명)

=> 조회된 행의 갯수를 세서 반환

 

COUNT() : 조회 결과에 해당하는 모든 행의 갯수를 다 세서 반환(기본값)
COUNT(칼럼명) : 제시한 칼럼값이 "NULL"이 아닌 것만 세서 갯수를 반환
COUNT(DISTINCT 칼럼명) : 제시한 칼럼값이 중복값이 있을 경우 하나로만 갯수를 세서 반환, NULL값 포함X

 

-- 전체사원수
SELECT COUNT(*)
FROM EMPLOYEE;

-- 여자 사원수
SELECT COUNT(*)
FROM EMPLOYEE
WHERE SUBSTR(EMP_NO, 8, 1) = '2';

 

-- 부서배치가 완료된 사원의 수
SELECT COUNT(*)
FROM EMPLOYEE
WHERE DEPT_CODE IS NOT NULL;

(==)
SELECT COUNT(DEPT_CODE) 
FROM EMPLOYEE;

 

-- 부서배치가 완료된 여자 사원 수
SELECT COUNT(DEPT_CODE) 
FROM EMPLOYEE
WHERE SUBSTR(EMP_NO, 8, 1) = '2';

 

-- 현재 사원들이 속한 부서의 갯수
SELECT COUNT(DISTINCT DEPT_CODE) 
FROM EMPLOYEE;  
-- NULL값 제외 중복값은 1개로 COUNT함.

 

-- 여자 사원의 수
SELECT COUNT( DECODE(SUBSTR(EMP_NO, 8, 1), 2, 1, NULL ) )
FROM EMPLOYEE;
-- 매개변수에 NULL값이 들어간 경우 갯수를 COUNT하지 않는다

 

 

-------------------- 실습문제 ---------------------
-- EMPLOYEE 테이블에서 직원명, 부서코드, 생년월일, 나이 조회
-- 단, 생년월일은 주민번호에서 추출해서 00년 00월 00일로 출력되게 하며
-- 나이는 주민번호에서 추출하여 날짜 데이터로 변환한 다음 계산하시오


-- 생년월일 추출 1)
SELECT EMP_NAME, DEPT_CODE, EMP_NO, 
        SUBSTR(EMP_NO, 1, 2) || '년' || SUBSTR(EMP_NO, 3, 2) || '월' || SUBSTR(EMP_NO, 5, 2) || '일'  생년월일
FROM EMPLOYEE;

-- 생년월일 추출 2)
SELECT EMP_NAME, DEPT_CODE, EMP_NO, 
        TO_CHAR(TO_DATE(SUBSTR(EMP_NO, 1, 6), 'YYMMDD'), 'YY"년" MM"월" DD"일"')
FROM EMPLOYEE;

-- 나이를 추출하여 날짜 데이터로 변환
SELECT EXTRACT(YEAR FROM SYSDATE) - EXTRACT(YEAR FROM TO_DATE(SUBSTR(EMP_NO, 1, 2), 'RRRR')) 나이
FROM EMPLOYEE;


-- 두가지 합치기
SELECT EMP_NAME, DEPT_CODE, EMP_NO, 
        TO_CHAR(TO_DATE(SUBSTR(EMP_NO, 1, 6), 'YYMMDD'), 'YY"년" MM"월" DD"일"') 생년월일,
        EXTRACT(YEAR FROM SYSDATE) - EXTRACT(YEAR FROM TO_DATE(SUBSTR(EMP_NO, 1, 2), 'RRRR')) 나이
FROM EMPLOYEE;