GROUP BY & HAVING

GROUP BY — one row per group

GROUP BY splits rows into groups — by course_id, city, or grade — then aggregates run on each group.

Every selected column must be in GROUP BY or inside an aggregate function.

Real-life example: GROUP BY is sorting exam papers into piles by class, then writing one average score per pile.

SELECT
  c.course_name,
  COUNT(e.enrollment_id) AS student_count,
  AVG(e.score) AS avg_score
FROM enrollments e
JOIN courses c ON e.course_id = c.course_id
GROUP BY c.course_name;

HAVING — filter groups after aggregation

WHERE filters rows before grouping. HAVING filters groups after GROUP BY — use it with aggregates like AVG(score) > 80.

Real-life example: HAVING is 'only show classes where average score is above 75' — you need the average first, then filter.

SELECT
  c.course_name,
  AVG(e.score) AS avg_score
FROM enrollments e
JOIN courses c ON e.course_id = c.course_id
GROUP BY c.course_name
HAVING AVG(e.score) > 80;