집계 함수와 GROUP BY — 통계 계산하기

-- 전체 행 수
SELECT COUNT(*) FROM employees;                -- 6

-- 특정 열 값이 있는 행 수 (NULL 제외)
SELECT COUNT(department) FROM employees;

-- 합계
SELECT SUM(salary) FROM employees;             -- 30200000

-- 평균
SELECT AVG(salary) FROM employees;             -- 5033333.33

-- 최대/최소
SELECT MAX(salary), MIN(salary) FROM employees;

-- 한 번에 여러 집계
SELECT
    COUNT(*) AS 직원수,
    SUM(salary) AS 급여총액,
    ROUND(AVG(salary), 0) AS 평균급여,
    MAX(salary) AS 최고급여,
    MIN(salary) AS 최저급여
FROM employees;

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

-- 결과
-- department | headcount
-- -----------|----------
-- 개발팀      | 3
-- 마케팅팀    | 2
-- 디자인팀    | 1

-- 부서별 평균 급여
SELECT
    department,
    COUNT(*) AS headcount,
    ROUND(AVG(salary), 0) AS avg_salary,
    SUM(salary) AS total_salary
FROM employees
GROUP BY department
ORDER BY avg_salary DESC;

-- 인원 2명 이상인 부서만
SELECT department, COUNT(*) AS headcount
FROM employees
GROUP BY department
HAVING COUNT(*) >= 2;

-- 평균 급여 500만 이상 부서
SELECT department, ROUND(AVG(salary), 0) AS avg_salary
FROM employees
GROUP BY department
HAVING AVG(salary) >= 5000000;

-- WHERE: GROUP BY 전에 적용 (원본 행 필터)
-- HAVING: GROUP BY 후에 적용 (그룹 필터)

-- 활성 직원만 대상으로 → 부서별 집계 → 인원 2명 이상 필터
SELECT department, COUNT(*) AS headcount
FROM employees
WHERE is_active = TRUE          -- 행 필터 (GROUP BY 전)
GROUP BY department
HAVING COUNT(*) >= 2;           -- 그룹 필터 (GROUP BY 후)

-- 고객별 주문 총액 (상위 5명)
SELECT
    c.name,
    COUNT(o.id) AS order_count,
    SUM(o.amount) AS total_spent
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
GROUP BY c.id, c.name
ORDER BY total_spent DESC NULLS LAST
LIMIT 5;

-- 부서별 소계 + 전체 합계
SELECT
    COALESCE(department, '전체') AS department,
    SUM(salary) AS total_salary
FROM employees
GROUP BY ROLLUP(department);

-- 결과
-- department | total_salary
-- -----------|-------------
-- 개발팀      | 17300000
-- 디자인팀    | 4800000
-- 마케팅팀    | 8100000
-- 전체        | 30200000   ← ROLLUP이 추가한 전체 합계