2024.08.13. 그룹함수, group by, having 절

* 그룹함수와 그룹화 *

(1) 그룹함수 

- 그룹함수란(=다중행함수)?  행그룹을 조작해서 하나의 결과값을 반환함.
- 그룹함수 종류 : sum, avg, min, max, count 
- 그룹함수 특징 : null값은 제외하고 작업함.

- min(행그룹) : 행그룹에서 최소값을 반환함. 모든 데이터타입에 사용 가능함.
- max(행그룹) : 행그룹에서 최대값을 반환함. 모든 데이터타입에 사용 가능함.

-- 숫자
select min(salary) as "최소 급여", max(salary) as "최대 급여" from employees;
-- 날짜
select min(HIRE_DATE) as "가장 오래된 입사일", max(HIRE_DATE) "가장 최근 입사일" from employees;
-- 문자
select min(LAST_NAME) as "name1", max(last_name) as "name2" from employees;

- sum(행그룹) : 행그룹의 합계를 반환함. 
- avg(행그룹) : 행그룹의 평균을 반환함. 
- count(행그룹) :

select sum(salary) as "급여 합계", avg(SALARY) as "평균 급여 " from employees;
select sum(salary) as "급여 합계", avg(SALARY) as "평균 급여 ", count(salary) from employees where job_id like "%REP%";

(Quiz) employees 테이블로부터 '전 직원'들을 기준으로 커미션 평균을 출력하시오.

select sum(commission_pct)/count(employee_id) as avg_comm from employees;
-- avg(ifnull(commission_pct,0) -- null 일대도 계산을 해야 하므로.

* count(*) : 행의 개수를 반환함. (null값 포함, 중복값 포함.)
- count(행그룹) : 행의 개수를 반환함. (null값 제외, 중복값 포함.)
- count(distinct 행그룹) : 행의 개수를 반환함.(null값 제외, 중복값 제외)

-- Q. employees 테이블로부터 전체 사원의 수를 출력하시오.
select count(*) from employees;
-- (==)
select count(employee_id) from employees;  
-- 특정컬럼 기준으로 count하는 것은 중간에 null 값이 있으면 count 안됨. 중복값은 count 됨.
-- PK는 Not null 조건이 있으므로 전부 count 한다. 즉 count 하는 기준컬럼으로 적합하다.
-- ----------------------------------------------------------------------------
-- Q. employees 테이블로부터 80번 부서에 소속된 사원의 수를 출력하시오.
select count(*) from employees where DEPARTMENT_ID = 80;
-- (==)
select count(DEPARTMENT_ID) from employees where DEPARTMENT_ID = 80;
-- ----------------------------------------------------------------------------
-- Q. employees 테이블로부터 커미션을 받는 사원의 수를 출력하시오.
select count(commission_pct) from employees;
-- ----------------------------------------------------------------------------
-- Q. employees 테이블로부터 80번 부서에 소속된 사원 중 commition을 받는 사원의 수를 출력하시오.
select count(commission_pct) from employees where department_id = 80;
-- ----------------------------------------------------------------------------
-- Q. employees 테이블에 있는 직원들이 소속된 부서의 수를 출력하시오.
select count(department_id) from employees; -- 결과 : 106 (사원수가 107명-부서결정 안된 신입사원1명 포함.)
select count(distinct department_id) from employees; -- 결과 : 11 (중복값 제외한 부서의 수를 카운팅함.)

 (2) group by 절

- 테이블 안에서 또다시 작은 그룹화를 나눠서 그룹함수를 적용할 경우 사용됨.
 [문법] 

select 컬럼1, 컬럼2, 컬럼3
from 테이블명
[ where 조건문 ]
[group by 컬럼명]
[order by 컬럼명  [asc | desc]];

(*중요!) group by 절과 그룹함수를 사용한 SQL구문 작성 시 문법(규칙)
- select절의 컬럼리스트 중 그룹함수에 포함된 컬럼과 그룹함수에 포함되지 않은 컬럼이 같이 출력되려면 적어도 그룹함수에 포함되지 않은 컬럼은 빠.짐.없.이 group by 절에 포함되어 있어야 문법 오류가 발생하지 않는다. 

-- ☑ employees 테이블에서 부서별 평균 급여를 출력하시오. (-> 부서별로 그룹화를 시켜야 한다.)
-- 내가 한것 : SELECT avg(department_id) FROM employees GROUP BY department_id; 
-- 내가 한것처럼 하면 department_id의 평균이 나온다.

select department_id, avg(salary)
from employees
group by department_id -- 그룹함수에 포함되지않은 컬럼을 group by 절에 포함시켰다.
order by department_id; -- 결과 12개 : 부서없는 신입사원 1명 포함.

select department_id, avg(salary)
from employees; -- 그룹함수에 포함되지않은 컬럼을 group by 절에 포함시지 않은 예.
-- 오류 : Error Code: 1140. In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column 'hr.employees.DEPARTMENT_ID'; this is incompatible with sql_mode=only_full_group_by

-- ☑ employees 테이블에서 부서 내 업무별 평균 급여를 출력하시오.
select department_id, -- 부서, 
	job_id,  -- 업무
        avg(salary)
from employees
group by department_id, job_id
order by department_id;

select department_id, job_id, sum(salary)
from employees
where department_id > 40 -- 부서번호가 50번 이상인 
group by department_id, job_id -- 부서내 업무별 연봉의 총 합계.
order by department_id;

 (3) having 절

[문법]

select 컬럼1, 컬럼2, 컬럼3
from 테이블명
[ where 조건문 ] -> 행 제한 조건문
[group by 컬럼명]
[ having 조건문 ] -> 행그룹 제한 조건문(그룹함수가 포함된 조건문)
[order by 컬럼명  [asc | desc]];

 * 오늘의 문제 *

-- 3. employees 테이블로부터 동일 업무(job_id)를 수행하는 직원 수를 출력하는 구문을 작성하시오
-- 내가 만든 쿼리 : 이렇게 하면 결과가 1개 나옴. 총 job_id 갯수만 나온다. 근데 문제가 원하는 것은 job_id를 수행하는 직원의 수를 구하라는 것이다. 
SELECT COUNT(distinct job_id) FROM employees; -- 결과 : 19
SELECT COUNT(job_id) FROM employees GROUP BY job_id; --(X): count(job_id)를 하면 업무만 count 함.
-- (==)
select job_id, count(employee_id) -- count(*)도 괜찮다.
from employees
group by job_id;


-- 6. employees 테이블로부터 사원의 총 수와 1995년, 1996년, 1997년, 1998년에 채용된 사원의 수를 표시하는 구문을 작성하시오.
-- 	select count(*), count(year(HIRE_DATE) = 1995)
select count(*),sum(if(year(HIRE_DATE)=1995,1,0)) "1995", -- 결과 : 4, 0대신 null을 해도 된다.
	count(if(year(HIRE_DATE)=1995,1,0)) "count_0_1995", -- 결과 : 107, if는 단일행 함수. 행당 점수를 준다. 0도 값이기 때문에 모든행을 반환해서 준다.
	count(if(year(HIRE_DATE)=1995,1,null)) "count_null_1995" -- 결과 : 4, null은 count하지않기때문에 null을 써야 나온다.                   
from employees;