Aggregate Functions: AVG, MAX, MIN, and COUNT Together

Overview

Aggregate functions turn a column of many values into a single summary value. AVG, MAX, MIN, and COUNT are the four you'll use most often, and they're frequently combined in a single query — for example, computing the average, highest, lowest, and total number of scores recorded for each course. Joining in a descriptive table (here, courses) lets you show a readable name instead of a bare course_id.

This pattern shows up anywhere you need a quick statistical snapshot: grade distributions, sales performance per product, response times per endpoint, and so on.

The Query

Step-by-Step Breakdown

1. FROM grades g JOIN courses c ON g.course_id = c.id — attach each grade row to its course so we can display a name instead of just an ID.
2. GROUP BY c.id, c.name — bucket grade rows by course. Both c.id and c.name are included in the GROUP BY even though c.id alone would uniquely determine the group, because most databases require every selected non-aggregated column to appear in the GROUP BY list.
3. AVG(g.score) — the mean score recorded for that course, across all students and terms present in the data.
4. MAX(g.score) / MIN(g.score) — the highest and lowest individual scores recorded for that course.
5. COUNT(*) — how many grade records exist for that course (not the number of distinct students — a student could appear more than once across terms).
6. ORDER BY avg_score DESC — courses with the highest average score first.

Variations

Restrict the same aggregation to a single term:

Filtering with WHERE g.term = 'Fall 2022' happens before grouping, so the averages reflect only that term's grade records rather than every term ever recorded for a course.

Common Mistakes

• Mixing up COUNT(*) and COUNT(column). They behave differently the moment NULL values are involved — COUNT(*) counts rows, COUNT(column) counts non-null values in that column.
• Averaging an already-averaged value. AVG of a column that's itself a per-group average (from a subquery, say) doesn't equal the true overall average unless every group has the same number of underlying rows.
• Forgetting GROUP BY needs every non-aggregated selected column. Adding c.name to the SELECT list without adding it to GROUP BY (when only grouping by c.id) will error in strict databases.
• Comparing MAX/MIN across groups of very different sizes. A course with only two grades recorded can have a misleadingly extreme MAX or MIN compared to a course with fifty grades — pair these with COUNT(*) so the sample size is visible.