GROUP BY & 집계함수

-- 전체 행 개수
SELECT COUNT(*) FROM employees;
-- 결과: 100

-- 특정 컬럼의 NULL이 아닌 값 개수
SELECT COUNT(commission) FROM employees;
-- 결과: 35 (commission이 NULL인 직원 제외)

-- 중복 제거한 개수
SELECT COUNT(DISTINCT department) FROM employees;
-- 결과: 5 (중복된 부서명 제외)

-- 조건부 카운트
SELECT COUNT(*) FROM employees WHERE salary >= 50000;
-- 결과: 42

-- 전체 급여 합계
SELECT SUM(salary) FROM employees;
-- 결과: 4850000

-- 특정 부서의 급여 합계
SELECT SUM(salary) FROM employees WHERE department = 'IT';
-- 결과: 1250000

-- NULL 값은 자동으로 무시됨
SELECT SUM(commission) FROM employees;
-- commission이 NULL인 행은 계산에서 제외

-- 조건부 합계 (CASE 활용)
SELECT SUM(CASE WHEN gender = 'M' THEN salary ELSE 0 END) AS male_salary_sum
FROM employees;

-- 전체 평균 급여
SELECT AVG(salary) FROM employees;
-- 결과: 48500.00

-- 소수점 자리수 조절
SELECT ROUND(AVG(salary), 2) FROM employees;
-- 결과: 48500.00

-- NULL 제외하고 평균 계산
SELECT AVG(commission) FROM employees;
-- commission이 NULL인 직원은 평균 계산에서 제외

-- 평균보다 높은 급여를 받는 직원
SELECT name, salary 
FROM employees 
WHERE salary > (SELECT AVG(salary) FROM employees);

-- 예시 데이터
-- salary: 50000, 60000, NULL, 40000

AVG(salary) = (50000 + 60000 + 40000) / 3 = 50000
-- NULL은 개수에서도 제외됨 (4가 아닌 3으로 나눔)

-- 최고 급여
SELECT MAX(salary) FROM employees;
-- 결과: 95000

-- 최저 급여
SELECT MIN(salary) FROM employees;
-- 결과: 28000

-- 최고 급여를 받는 직원 정보 (서브쿼리 활용)
SELECT name, salary 
FROM employees 
WHERE salary = (SELECT MAX(salary) FROM employees);

-- 부서별 최고/최저 급여 차이
SELECT 
    department,
    MAX(salary) - MIN(salary) AS salary_gap
FROM employees
GROUP BY department;

-- 날짜형에도 사용 가능
SELECT MAX(hire_date) AS latest_hire FROM employees;
-- 결과: 2024-12-15 (가장 최근 입사일)

-- 문자열에도 사용 가능 (사전순)
SELECT MIN(name) FROM employees;
-- 결과: 'A'로 시작하는 이름 중 가장 앞선 이름

SELECT 
    COUNT(*) AS 총직원수,
    COUNT(DISTINCT department) AS 부서수,
    SUM(salary) AS 급여총액,
    AVG(salary) AS 평균급여,
    MAX(salary) AS 최고급여,
    MIN(salary) AS 최저급여,
    MAX(salary) - MIN(salary) AS 급여차이
FROM employees;

-- 부서별 직원 수
SELECT department, COUNT(*) AS employee_count
FROM employees
GROUP BY department;

-- 부서별, 직급별 그룹화
SELECT 
    department, 
    position, 
    COUNT(*) AS cnt,
    AVG(salary) AS avg_sal
FROM employees
GROUP BY department, position
ORDER BY department, position;

-- ✘ 잘못된 쿼리 (에러 발생)
SELECT department, name, COUNT(*)
FROM employees
GROUP BY department;

-- ✔ 올바른 쿼리 1: 집계함수 사용
SELECT department, COUNT(*) AS cnt
FROM employees
GROUP BY department;

-- ✔ 올바른 쿼리 2: GROUP BY에 포함
SELECT department, name, salary
FROM employees
GROUP BY department, name, salary;

-- ✔ 올바른 쿼리 3: 서브쿼리로 최고 급여자만 선택
SELECT department, name, salary
FROM employees e1
WHERE salary = (
    SELECT MAX(salary) 
    FROM employees e2 
    WHERE e2.department = e1.department
);

-- 급여가 40000 이상인 직원들만 부서별로 그룹화
SELECT 
    department, 
    COUNT(*) AS high_earners,
    AVG(salary) AS avg_salary
FROM employees
WHERE salary >= 40000  -- 그룹화 전 필터링
GROUP BY department;

-- 평균 급여가 50000 이상인 부서만 조회
SELECT 
    department, 
    AVG(salary) AS avg_salary,
    COUNT(*) AS emp_count
FROM employees
GROUP BY department
HAVING AVG(salary) >= 50000;

-- WHERE: 그룹화 전 행 필터링
SELECT department, COUNT(*) AS cnt
FROM employees
WHERE salary >= 50000  -- 개별 행 조건
GROUP BY department;

-- HAVING: 그룹화 후 그룹 필터링
SELECT department, COUNT(*) AS cnt
FROM employees
GROUP BY department
HAVING COUNT(*) >= 10;  -- 그룹 조건

-- 2020년 이후 입사자 중, 부서별 평균 급여가 45000 이상인 부서
SELECT 
    department, 
    COUNT(*) AS emp_count,
    AVG(salary) AS avg_salary
FROM employees
WHERE hire_date >= '2020-01-01'  -- 개별 행 필터링 (그룹화 전)
GROUP BY department
HAVING AVG(salary) >= 45000;     -- 그룹 필터링 (그룹화 후)

-- 직원이 5명 이상이고, 최고 급여가 80000 이상인 부서
SELECT 
    department,
    COUNT(*) AS emp_count,
    MAX(salary) AS max_salary
FROM employees
GROUP BY department
HAVING COUNT(*) >= 5 AND MAX(salary) >= 80000;

-- 급여 표준편차가 큰 부서 (급여 편차가 심한 부서)
SELECT 
    department,
    STDDEV(salary) AS salary_stddev
FROM employees
GROUP BY department
HAVING STDDEV(salary) > 15000;

-- 급여 합계가 상위 3개 부서
SELECT 
    department,
    SUM(salary) AS total_salary
FROM employees
GROUP BY department
ORDER BY SUM(salary) DESC
LIMIT 3;

SELECT 
    department AS 부서,
    COUNT(*) AS 직원수,
    ROUND(AVG(salary), 0) AS 평균급여,
    MAX(salary) AS 최고급여,
    MIN(salary) AS 최저급여,
    MAX(salary) - MIN(salary) AS 급여편차,
    SUM(salary) AS 급여총액
FROM employees
GROUP BY department
HAVING COUNT(*) >= 5  -- 5명 이상인 부서만
ORDER BY 평균급여 DESC;

SELECT 
    YEAR(hire_date) AS 입사년도,
    department AS 부서,
    COUNT(*) AS 입사자수,
    ROUND(AVG(salary), 0) AS 평균초봉
FROM employees
WHERE hire_date >= '2020-01-01'
GROUP BY YEAR(hire_date), department
HAVING COUNT(*) >= 3
ORDER BY 입사년도 DESC, 부서;

SELECT 
    gender AS 성별,
    position AS 직급,
    COUNT(*) AS 인원,
    AVG(salary) AS 평균급여,
    MIN(salary) AS 최저급여,
    MAX(salary) AS 최고급여
FROM employees
GROUP BY gender, position
ORDER BY gender, position;

-- 급여 구간별 직원 수
SELECT 
    department,
    COUNT(*) AS 총인원,
    SUM(CASE WHEN salary < 40000 THEN 1 ELSE 0 END) AS 저급여,
    SUM(CASE WHEN salary BETWEEN 40000 AND 60000 THEN 1 ELSE 0 END) AS 중급여,
    SUM(CASE WHEN salary > 60000 THEN 1 ELSE 0 END) AS 고급여,
    ROUND(AVG(CASE WHEN salary < 40000 THEN salary END), 0) AS 저급여평균
FROM employees
GROUP BY department;

-- 각 부서의 평균 급여보다 높은 급여를 받는 직원 수
SELECT 
    e.department,
    COUNT(*) AS 평균이상직원수
FROM employees e
WHERE e.salary > (
    SELECT AVG(salary) 
    FROM employees 
    WHERE department = e.department
)
GROUP BY e.department;

-- 부서별 소계 + 전체 총계
SELECT 
    department,
    COUNT(*) AS cnt,
    SUM(salary) AS total
FROM employees
GROUP BY department WITH ROLLUP;

-- GROUP BY: 그룹당 하나의 행
SELECT department, AVG(salary) AS avg_salary
FROM employees
GROUP BY department;
-- 결과: 5개 행 (부서 수만큼)

-- 윈도우 함수: 모든 행 유지
SELECT 
    name, 
    department, 
    salary,
    AVG(salary) OVER (PARTITION BY department) AS dept_avg
FROM employees;
-- 결과: 100개 행 (전체 직원 수만큼)

-- ✘ 에러 발생
SELECT 
    department, 
    AVG(salary) AS avg_sal
FROM employees
GROUP BY department
HAVING avg_sal > 50000;  -- 별칭 사용 불가

-- ✔ 올바른 방법
SELECT 
    department, 
    AVG(salary) AS avg_sal
FROM employees
GROUP BY department
HAVING AVG(salary) > 50000;  -- 원본 식 사용

SELECT department, COUNT(*) AS cnt    -- 5. 결과 출력
FROM employees                        -- 1. 테이블 선택
WHERE salary >= 40000                 -- 2. 행 필터링
GROUP BY department                   -- 3. 그룹화
HAVING COUNT(*) >= 10                 -- 4. 그룹 필터링
ORDER BY cnt DESC                     -- 6. 정렬
LIMIT 3;                              -- 7. 행 수 제한

-- ✘ 에러 (MySQL 5.7 이상)
SELECT department, COUNT(*)
FROM employees;

-- ✔ 올바른 방법
SELECT department, COUNT(*)
FROM employees
GROUP BY department;

-- ✘ 에러
SELECT department
FROM employees
WHERE AVG(salary) > 50000
GROUP BY department;

-- ✔ HAVING 사용
SELECT department
FROM employees
GROUP BY department
HAVING AVG(salary) > 50000;

-- ✘ 에러
SELECT department, name, COUNT(*)
FROM employees
GROUP BY department;

-- ✔ 집계함수 사용 또는 GROUP BY에 추가
SELECT department, COUNT(name) AS name_count
FROM employees
GROUP BY department;