[SQL-Oracle]함수(Function) - 그룹함수 ★중요★ - count, max, min, sum, avg, group by, having

⚫그룹함수 : 매개변수로 다수의 값을 전달받아 가공하여 처리 결과값을 반환하는 함수

 

🔘 COUNT(컬럼명) : 매개변수로 테이블에 저장된 검색행의 모든 컬럼값을 전달받아 컬럼값의 갯수를 반환하는 함수

-- 검색행의 갯수 반환
SELECT COUNT(EMPNO) FROM EMP;

--그룹함수를 다른 검색대상과 같이 사용할 경우 그룹함수 반환값의 행갯수와 검색대상의 행갯수가 다르면 에러 발생
SELECT COUNT(EMPNO),EMPNO FROM EMP;

--그룹함수는 NULL을 값으로 처리하지 않기 때문에 NULL를 제외한 결과값 반환
SELECT COUNT(EMPNO),COUNT(COMM) FROM EMP;

--COUNT 함수는 컬럼명 대신 [*] 기호를 사용하여 모든 컬럼을 표현하여 검색행의 갯수 반환
SELECT COUNT(*) FROM EMP;

 

*MAX 와 MIN은 서브쿼리할 때 많이 씀
🔘 MAX(컬럼명) : 매개변수로 테이블에 저장된 검색행의 모든 컬럼값을 전달받아 컬럼값의 최대값을 반환하는 함수

SELECT MAX(SAL) FROM EMP;
SELECT MAX(ENAME) FROM EMP;
SELECT MAX(HIREDATE) FROM EMP;

🔘 MIN(컬럼명) : 매개변수로 테이블에 저장된 검색행의 모든 컬럼값을 전달받아 컬럼값의 최소값을 반환하는 함수

SELECT MIN(SAL) FROM EMP;
SELECT MIN(ENAME) FROM EMP;
SELECT MIN(HIREDATE) FROM EMP;

🔘 SUM(컬럼명) : 매개변수로 테이블에 저장된 검색행의 모든 컬럼값(숫자값)을 전달받아 컬럼값의 합계를 계산하여 반환하는 함수

SELECT SUM(SAL) FROM EMP;

🔘 AVG(컬럼명) : 매개변수로 테이블에 저장된 검색행의 모든 컬럼값(숫자값)을 전달받아 컬럼값의 평균을 계산하여 반환하는 함수

SELECT AVG(SAL) FROM EMP;
SELECT ROUND(AVG(SAL),2) FROM EMP;
--ROUND보다 CEIL 또는 FLOOR 많이 씀

예제

--EMP 테이블에 저장된 모든 사원의 성과급을 제공받아 성과급의 평균값을 계산하여 검색
--그룹함수는 NULL을 제외한 컬럼값만 이용하므로 성과급이 NULL이 아닌 사원에 대한 평균 성과급 검색 - 실행오류
SELECT AVG(COMM) FROM EMP;
--성과급이 NULL인 경우 0으로 변환하여 처리해 모든 사원의 평균 성과급 검색
SELECT AVG(NVL(COMM,0)) FROM EMP;
SELECT CEIL(AVG(NVL(COMM,0))) "평균 성과급" FROM EMP;

--EMP 테이블에 저장된 모든 사원에 대한 인원수 검색
SELECT COUNT(*) FROM EMP;

--EMP 테이블에 저장된 사원을 부서번호로 구분하여 인원수 검색
SELECT DISTINCT DEPTNO FROM EMP;
SELECT COUNT(*) FROM EMP WHERE DEPTNO=10;--10번 부서에 근무하는 사원의 인원수
SELECT COUNT(*) FROM EMP WHERE DEPTNO=20;--20번 부서에 근무하는 사원의 인원수
SELECT COUNT(*) FROM EMP WHERE DEPTNO=30;--30번 부서에 근무하는 사원의 인원수

🔘 ⭐GROUP BY : 그룹함수 사용할 때 검색행의 컬럼값으로 그룹을 나누어 검색하는 기능 제공
--검색행의 컬럼값이 같은 경우 하나의 그룹으로 묶어 그룹함수의 매개변수에 컬럼값을 제공하여 결과값 반환
형식) SELECT 그룹함수(컬럼명)[,검색대상,...] FROM 테이블명 [WHERE 조건식]
      GROUP BY {컬럼명|연산식|함수}, {컬럼명|연산식|함수}, ...
      ORDER BY {컬럼명|연산식|별칭|COLUMN_INDEX} {ASC|DESC}, ...

--EMP 테이블에 저장된 모든 사원을 부서번호로 구분하여 인원수 검색
SELECT COUNT(*) FROM EMP GROUP BY DEPTNO;

--GROUP BY에서 사용한 표현식(컬럼명|연산식|함수)은 그룹함수와 같이 검색대상으로 표현하여 검색 가능
SELECT DEPTNO,COUNT(*) FROM EMP GROUP BY DEPTNO;
--GROUP BY의 표현식에서 컬럼의 별칭을 사용할 경우 에러 발생
SELECT DEPTNO DNO,COUNT(*) FROM EMP GROUP BY DNO;

 

예제

--EMP 테이블에 저장된 모든 사원을 업무로 구분하여 인원수, 급여 합계, 급여 평균 검색
SELECT JOB, COUNT(*), SUM(SAL), CEIL(AVG(SAL)) FROM EMP GROUP BY JOB;
SELECT JOB, COUNT(*) CNT, SUM(SAL) SUM_SAL, CEIL(AVG(SAL)) AVG_SAL FROM EMP GROUP BY JOB;

--EMP 테이블에서 업무가 PRESIDENT인 사원을 제외한 나머지 사원을 업무로 구분하여 업무, 인원수, 급여 합계, 급여 평균을
--급여 평균으로 내림차순 정렬
SELECT JOB, COUNT(*) CNT, SUM(SAL) SUM_SAL, CEIL(AVG(SAL)) AVG_SAL
    FROM EMP
    WHERE JOB<>('PRESIDENT')
    GROUP BY JOB
    ORDER BY AVG_SAL DESC;

 

    
🔘 HAVING : GROUP BY에 의해 생성된 그룹에서 조건식이 참(TRUE)인 그룹만 검색하는 기능 제공
--검색행의 컬럼값이 같은 경우 하나의 그룹으로 묶어 그룹함수의 매개변수에 컬럼값을 제공하여 결과값 반환
형식) SELECT 그룹함수(컬럼명)[,검색대상,...] FROM 테이블명 [WHERE 조건식]
      GROUP BY {컬럼명|연산식|함수}, {컬럼명|연산식|함수}, ... HAVING 그룹조건식
      [ORDER BY {컬럼명|연산식|별칭|COLUMN_INDEX} {ASC|DESC}, ...]

 

예제
--EMP 테이블에서 모든 사원을 업무로 구분하여 업무, 인원수, 급여 합계, 급여 평균을 급여 평균으로 내림차순 정렬하여 검색하되 업무가 PRESIDENT인 그룹은 제외하고 검색

SELECT JOB, COUNT(*) CNT, SUM(SAL) SUM_SAL, CEIL(AVG(SAL)) AVG_SAL
    FROM EMP
    GROUP BY JOB HAVING JOB<>('PRESIDENT')
    ORDER BY AVG_SAL DESC;

    
--EMP테이블에 저장된 모든 사원을 부서번호로 구분하여 부서번호, 급여 합계 검색하되 급여 합계가 9000 이상인 그룹만 검색

 SELECT DEPTNO, SUM(SAL) SUM_SAL 
    FROM EMP
    GROUP BY DEPTNO HAVING SUM(SAL)>=9000;