에듀테크 프로젝트 대시보드
집계와 GROUP BY
여러 행을 하나의 값으로 요약합니다. GROUP BY 로 그룹을 나눕니다.
SELECT
count(*) AS 전체행, -- NULL 포함 모든 행
count(score) AS 점수있는행, -- NULL 제외
count(DISTINCT class_id) AS 반개수,
avg(score), min(score), max(score), sum(score),
round(avg(score), 1) AS 평균,
percentile_cont(0.5) WITHIN GROUP (ORDER BY score) AS 중앙값
FROM exams;SELECT class_id, count(*) AS n, avg(score) AS avg_score
FROM exams
WHERE exam_date >= '2026-03-01'
GROUP BY class_id
HAVING count(*) >= 5 -- 그룹 필터는 HAVING (집계 결과 기준)
ORDER BY avg_score DESC;WHERE 는 그룹 전 행 필터, HAVING 은 그룹 후 필터.
SELECT
class_id,
count(*) AS 응시,
count(*) FILTER (WHERE score >= 60) AS 합격,
avg(score) FILTER (WHERE score >= 60) AS 합격자평균
FROM exams
GROUP BY class_id;예전엔 sum(CASE WHEN ... THEN 1 ELSE 0 END) 로 하던 것을 깔끔하게 대체합니다.
SELECT class_id,
string_agg(name, ', ' ORDER BY name) AS 명단,
array_agg(id ORDER BY score DESC) AS id목록
FROM students
GROUP BY class_id;
-- 소계·총계
SELECT class_id, grade, count(*)
FROM students GROUP BY ROLLUP (class_id, grade);