Aggregate Functions and GROUP BY

Aggregate functions

SELECT COUNT(*) FROM students;              -- total rows
SELECT AVG(score) FROM students;            -- average score
SELECT MAX(score), MIN(score) FROM students;
SELECT SUM(score) FROM students;

An aggregate function collapses many rows down into a single summary value. Used alone like this, they summarize the entire table at once — GROUP BY is what lets you get a separate summary per category instead.

Grouping rows

SELECT course, COUNT(*) AS total_students, AVG(score) AS avg_score
FROM students
GROUP BY course;

This answers: “for each course, how many students and what’s the average score?” — one summary row per distinct value in the grouped column. Every column in the SELECT list that isn’t wrapped in an aggregate function must appear in the GROUP BY clause, or the database will raise an error, since it wouldn’t know which of the many possible values to show for that column in each group.

Filtering groups with HAVING

SELECT course, AVG(score) AS avg_score
FROM students
GROUP BY course
HAVING AVG(score) >= 80;

WHERE filters individual rows before grouping happens; HAVING filters entire groups after aggregation. You cannot use an aggregate function like AVG() inside a WHERE clause — that’s precisely the situation HAVING exists to handle.

Combining WHERE, GROUP BY, and HAVING

SELECT course, AVG(score) AS avg_score
FROM students
WHERE score IS NOT NULL
GROUP BY course
HAVING COUNT(*) >= 3
ORDER BY avg_score DESC;

This is a realistic full query: filter out incomplete rows first (WHERE), group the rest by course, keep only courses with at least 3 students (HAVING), and sort the results by average score. SQL clauses always execute in this logical order — WHERE, then GROUP BY, then HAVING, then ORDER BY — even though you write SELECT first.