2.3 GROUP BY and HAVING

GROUP BY folds many rows into groups, then computes summaries for each group. Common aggregate functions include:

• COUNT(*): count rows.
• SUM(amount): sum values.
• AVG(score): average.
• MIN(value), MAX(value): smallest and largest values.

From rows to groups

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

This groups rows by course_id, outputting one summary row per course.

HAVING filters grouped results

SELECT course_id, COUNT(*) AS student_count
FROM enrollments
GROUP BY course_id
HAVING COUNT(*) >= 2;

WHERE filters raw rows before grouping. HAVING filters the grouped result.

A portable aggregation habit

SELECT c.title,
       COUNT(*) AS student_count,
       AVG(s.score) AS avg_score
FROM courses AS c
JOIN students AS s
  ON s.course_id = c.course_id
GROUP BY c.title
HAVING AVG(s.score) >= 80
ORDER BY avg_score DESC;

Portable habit: non-aggregate fields in SELECT should appear in GROUP BY or be derived from grouped values.

Aggregates are not detail rows

After aggregation, row-level detail is gone. If a course average is 88, that summary row no longer tells you each student's name. When you need detail and summary together, return to the raw tables or learn window functions.