GROUP BY and Aggregate Functions - SQL Tutorial

Aggregate functions summarise data across multiple rows into a single result — counting, summing, averaging, finding min/max.

The five aggregate functions

SQL

SELECT
    COUNT(*)            AS total_rows,
    COUNT(DISTINCT course) AS unique_courses,
    AVG(score)          AS average_score,
    SUM(score)          AS total_score,
    MIN(score)          AS lowest_score,
    MAX(score)          AS highest_score
FROM enrollments;

Function	What it does	Ignores NULLs?
`COUNT(*)`	Counts all rows	No
`COUNT(col)`	Counts non-NULL values in `col`	Yes
`AVG(col)`	Average of non-NULL values	Yes
`SUM(col)`	Sum of non-NULL values	Yes
`MIN(col)` / `MAX(col)`	Smallest / largest value	Yes

GROUP BY — Grouping rows

SQL

-- Average score per course
SELECT course, AVG(score) AS avg_score
FROM enrollments
GROUP BY course;

-- Student count per course
SELECT course, COUNT(*) AS student_count
FROM enrollments
GROUP BY course;

GROUP BY collapses all rows with the same value(s) into one result row. Every non-aggregated column in SELECT must appear in GROUP BY.

HAVING — Filtering groups

WHERE filters individual rows before grouping. HAVING filters groups after aggregation.

SQL

-- Courses with average score above 70
SELECT course, AVG(score) AS avg_score
FROM enrollments
GROUP BY course
HAVING AVG(score) > 70;

-- Courses with more than 5 students
SELECT course, COUNT(*) AS student_count
FROM enrollments
GROUP BY course
HAVING COUNT(*) > 5;

Multiple groupings

SQL

-- Average score per course and per student
SELECT s.name, e.course, AVG(e.score) AS avg
FROM students s
JOIN enrollments e ON s.id = e.student_id
GROUP BY s.id, s.name, e.course
ORDER BY s.name, avg DESC;

Rollup and Cube

SQL

-- Subtotals per course, plus a grand total
SELECT course, SUM(score) AS total
FROM enrollments
GROUP BY ROLLUP(course);

The execution order

SQL

SELECT column, AGG(column)       -- 4. compute results
FROM table                        -- 1. read tables
WHERE condition                   -- 2. filter rows
GROUP BY column                   -- 3. group remaining rows
HAVING AGG(condition)             -- 5. filter groups
ORDER BY column                   -- 6. sort
LIMIT n;                          -- 7. cut

Tips

WHERE filters rows; HAVING filters groups.
You can't use WHERE with aggregate functions — that's what HAVING is for.
COUNT(*) counts all rows; COUNT(column) counts non-NULL values.
Always use GROUP BY when mixing aggregated and non-aggregated columns.

Next: modifying.