[MySQL] #12 SQL 그룹 함수 / GROUP BY / ROLLUP

/*
그룹함수(집계함수)
    count, sum, max, min, avg
*/

-- count
-- 전체 사원수를 조회

SELECT count(*) FROM emp;

# 프로그램적으로 더 좋은 기법

SELECT count(-1) FROM emp;  # -1 마지막 컬럼 혹은 기본키로 조회하기

-- 그룹함수는 null값을 제외하고 처리한다.. 이때 COUNT(*)는 null 값도 조회
-- sum, avg 숫자 데이터에만 적용 / max, min, count 모든 데이터에 적용

SELECT round(avg(sal)), sum(sal), min(sal), max(sal)
FROM emp;

-- 입사한지 가장 오래된 사원과 가장 최근에 입사한 사원의 입사일을 조회

SELECT min(hiredate), max(hiredate)
FROM emp;

-- 부서번호가 10번 이거나 20번인 사원수를 조회

SELECT count(empno)
FROM emp
WHERE deptno IN (10, 20);

-- 모든 사원의 보너스의 평균

SELECT round(avg(ifnull(comm, 0))) bonus
FROM emp;

# 그룹함수 사용 시 null 값 주의해서 사용 <- 위의 경우 ifnull 처리 안해주면 null인 행은 건너뛰므로 comm 값을 가진 사람들끼리의 평균을 구함

-- emp 테이블에서 부서의 갯수

SELECT count(DISTINCT deptno)
FROM emp;

 

SELECT deptno, AVG(sal)
FROM emp;

# 그룹함수에 명시되지 않은 컬럼이 SELECT절에 나열되서는 안된다.
# 사용하고 싶다면 GROUP BY 사용할 것 -> 더 정교한 분석 가능

SELECT deptno, round(avg(sal))
FROM emp
GROUP BY deptno;

# MySQL의 경우 GROUP BY 절에 Alias 사용 가능, Oracle은 에러남 (WHERE절은 둘 다 사용 불가, ORDER BY는 둘 다 사용 가능)

SELECT deptno DNumber, round(avg(sal)) AvgSalary
FROM emp
WHERE deptno != 10
GROUP BY DNumber
ORDER BY DNumber;

-- 입사년도별 사원의 인원수 출력

SELECT year(hiredate) 입사년도, count(empno) 인원수
FROM emp
GROUP BY year(hiredate)
ORDER BY year(hiredate);

# GROUP BY에 Alias 사용 가능하므로.. 아래가 더 가독성이 좋음 / MySQL만 사용한다면 GROUP BY에 Alias 사용 추천~

SELECT year(hiredate) 입사년도, count(empno) 인원수
FROM emp
GROUP BY 입사년도
ORDER BY 1;

-- WHERE절에서는 그룹함수 사용 불가
-- 부서별 평균급여가 2000 달러 이상인 부서번호와 평균급여를 조회

SELECT deptno, round(avg(sal)) 평균급여
FROM emp
GROUP BY deptno
HAVING 평균급여 >= 2000
ORDER BY 1;

-- 최대급여가 2900 달러가 넘는 부서들의 최대 급여를 출력

SELECT deptno, max(sal) 최대급여
FROM emp
GROUP BY deptno
HAVING 최대급여 > 2900
ORDER BY 1;

 

/*
ROLLUP: GROUP BY의 확장이다.
    ROLLUP은 그룹별 중간합계나 그룹별 총합이 필요한 경우에 사용되는 기능.
    ROLLUP의 인수값은 계층구조를 이루기 때문에 인수 순서가 바뀌면 수행결과도 달라진다.
*/

-- 부서별 인원수, 급여합 조회시 ROLLUP을 사용해서 총집계를 구한다.

SELECT deptno, count(empno), sum(sal)
FROM emp
GROUP BY deptno WITH ROLLUP;

-- 부서별, 업무별 급여의 합

SELECT deptno, job, sum(sal)
FROM emp
GROUP BY deptno, job WITH ROLLUP;