/*
* 그룹(집계) 함수
* 1.count() sum() avg() max() min()...
* 2.Group by - 그룹함수가 적용되기 전 그룹화하는 작업 기준
* 3. Having - 그룹함수에 적용하는 조건절
* */
-- 1. 그룹함수 : 형식 - 컬럼리스트가 위치하는 곳에 함수 호출
SELECT count(*) FROM employee; -- 20개 출력
SELECT count(salary) FROM employee; -- 19개 출력 이유는 count 함수는 자동적으로 NULL 값은 제외하고 카운트한다
SELECT count(ifnull(salary,0)) FROM employee -- ifnull로 처리해서 NULL 값을 없애주는것도 방법이다.(실무에서는 서브쿼리사용함)
SELECT count(emp_id) FROM employee; -- 전체데이터기준 : * , 컬럼을 pk로 설정하는 방법
-- sum(숫자데이터 컬럼)
-- 총급여를 출력, 3자리로 구분 , 만원 단위 붙이기
SELECT concat(format(sum(salary),0),'만원') AS 총급여 FROM employee;
-- avg(숫자데이터칼럼)
-- 평균급여를 ㅇ출력, 3자리로 구분, 만원을 추가
-- 단 소수점이 있는 경우 모두 절삭 (소수점자리 몇째까지 출력이 필요하다 그러면 truncate를 사용하면 됨)
SELECT concat( format(floor(avg(salary)),0) ,'만원') AS 평균급여 FROM employee;
-- 총급여와 평균급여 출력
SELECT sum(salary)AS 총급여, avg(salary) AS 평균급여 FROM employee
-- 총급여, 평균급여 , 최대연봉, 최저연봉
SELECT sum(salary)AS 총급여, avg(salary) AS 평균급여, max(salary) AS 최대연봉, min(salary) AS 최저연봉 FROM employee
-- 가장빠른 입사일, 가장최근 입사일
SELECT min(hire_date),max(hire_date) FROM employee;
-- 부서별 총연봉, 평균연봉, 최대연봉, 최저연봉
-- ~~ 별 이라고하면 group by 를 생각해야한다
SELECT * FROM employee order BY dept_id
SELECT sum(salary),avg(salary),max(salary),min(salary) FROM employee GROUP BY dept_id
-- 그룹별 분류는 되었지만 숫자만 나와서 어떤 그룹인지 알수가 없는 결과가 출력이 된다
SELECT dept_id ,sum(salary),avg(salary),max(salary),min(salary) FROM employee GROUP BY dept_id
-- 이처럼 group by 에 사용된 함수는 select 뒤에서도 사용이 가능하다
--
-- pk 값으로 group by 를 쓰면 중복되는 값이 없기 때문에 비효율적임
SELECT emp_id , sum(salary),avg(salary) FROM employee GROUP BY emp_id
-- 입사년도별 총연봉, 평균연봉, 최대연봉, 최저연봉 , 그리고 부서를 출력하시오
-- group by 에 출력되지 않은 일반 함수는 출력 될 수 없다.
SELECT concat(LEFT(hire_date,4),'년') AS 입사년도,dept_id , count(*) ,sum(salary),avg(salary),max(salary),min(salary)
FROM employee GROUP BY concat(LEFT(hire_date,4),'년'),dept_id
-- 입사월별 사원수,총연봉,평균연봉,최대연봉,최저연봉
SELECT concat(substring(hire_date,6,2),'월') AS 입사월,concat(count(*),'명') AS 사원수,
sum(salary),avg(salary),max(salary),min(salary)
FROM employee
GROUP BY concat(substring(hire_date,6,2),'월')
ORDER BY concat(substring(hire_date,6,2),'월')
-- 부서별 사원수가 2명인 부서만 출력해주세요
SELECT dept_id,count(*) AS 사원수 FROM employee GROUP BY dept_id HAVING count(*) = 2
SELECT dept_id,count(*) AS 사원수 FROM employee GROUP BY dept_id HAVING 사원수 = 2
-- having 은 where 조건절과 다르게 그룹핑과 솔팅이 되고나서 마지막에 실행되기 때문에 as 로 조건을 줘도 실행이 가능하다
-- 입사년도별 평균급여가 5000 이상인 해당년도만 출력
SELECT LEFT(hire_date,4) AS year,truncate(avg(salary),0) FROM employee GROUP BY LEFT(hire_date,4) HAVING avg(salary) > 5000
-- 부서별 최고급여가 6000 이상 7000미만인 부서 출력
SELECT dept_id,max(salary) FROM employee GROUP BY dept_id HAVING max(salary) BETWEEN 6000 AND 6999
-- rollup 함수를 사용하여 총합계, 소계를 출력
-- 부서별 총합계를 출력
SELECT dept_id ,format(sum(salary),0) FROM employee GROUP BY dept_id WITH ROLLUP
-- 년도별 각 부서의 사원수와 총연봉을 출력
-- 단 사원이 없는 부서는 그룹핑하지 않음
SELECT LEFT(hire_date,4) AS year,dept_id , count(*) AS 사원수 , sum(salary)
FROM employee WHERE salary IS NOT NULL
GROUP BY year,dept_id WITH ROLLUP
SELECT * FROM employee
-- 부서별, 사원수(성별구분) 출력
SELECT dept_id , gender, count(*) 사원수 FROM employee GROUP BY dept_id ,gender WITH ROLLUP
-- 부서별, 총연봉 출력
SELECT ifnull(dept_id,'소계') ,sum(salary) FROM employee WHERE salary IS NOT null GROUP BY dept_id WITH ROLLUP
/*
* 테이블 합치기 : union(disticnt), union all
* 형식 : select 쿼리
* UNION / UNION ALL
* SELECT 쿼리
* - 쿼리 실행시에만 하나의 테이블로 출력됨 (물리적인 테이블 생성x)
* - 하나로 합쳐지는 컬럼명과 타입이 동일해야 함
* */
-- 영업부,정보시스템 부서의 사원아이디 , 사원명 , 부서아이디, 입사일을 출력
-- union (중복제외) 중복까지 체크하기 때문에 union all 보다 더 성능이 떨어짐
SELECT emp_id,emp_name ,dept_id ,hire_date , concat('부서아이디 :',dept_id) did FROM employee
WHERE dept_id = 'MKT'
UNION
SELECT emp_id,emp_name ,dept_id ,hire_date,concat('부서아이디 :',dept_id) did FROM employee
WHERE dept_id = 'SYS'
UNION
SELECT emp_id,emp_name ,dept_id ,hire_date,concat('부서아이디 :',dept_id) did FROM employee
WHERE dept_id = 'MKT'
-- union all (중복포함)
SELECT emp_id,emp_name ,dept_id ,hire_date , concat('부서아이디 :',dept_id) did FROM employee
WHERE dept_id = 'MKT'
UNION ALL
SELECT emp_id,emp_name ,dept_id ,hire_date,concat('부서아이디 :',dept_id) did FROM employee
WHERE dept_id = 'SYS'
UNION ALL
SELECT emp_id,emp_name ,dept_id ,hire_date,concat('부서아이디 :',dept_id) did FROM employee
WHERE dept_id = 'MKT'
-- 서브쿼리 맛보기(스칼라 서브쿼리/인라인뷰/서브쿼리) 맛보기
-- SELECT 컬럼리스트 FROM 테이블명 WHERE 조건절
-- (스칼라 서브쿼리) (인라인뷰) (서브쿼리)
-- 스칼라서브쿼리는 성능문제로 잘 사용안함 , sql 튜닝할때 우선순위로 수정
-- 홍길동 사원이 속한 부서의 이름을 조회
SELECT * FROM department WHERE dept_id = (SELECT dept_id FROM employee WHERE emp_name = '홍길동' );