Aggregating with GROUP BY

Aggregate functions summarize many rows into one value, COUNT, SUM, AVG, MIN, MAX. GROUP BY buckets rows before aggregating.

SELECT course_title, COUNT(*) AS student_count
FROM enrollments
GROUP BY course_title;

This counts how many enrollment rows exist per course_title, one summary row per group.

Filtering groups with HAVING

WHERE filters individual rows before grouping, HAVING filters the grouped results after aggregating:

SELECT course_title, AVG(xp) AS average_xp
FROM enrollments
JOIN students ON students.id = enrollments.student_id
GROUP BY course_title
HAVING AVG(xp) > 50;

You can't write WHERE AVG(xp) > 50, the average doesn't exist yet at the point WHERE runs, that's exactly what HAVING is for.