Aggregation, GROUP BY, HAVING

Chapter 4 16 code blocks 3704 words

So far every query returned rows that already existed. Aggregation does something different: it collapses many rows into one. "How many students?", "What is the average grade?", "Which course earns the most?" — these are the questions analytics actually asks, and this chapter is where SQL starts being an analytics language rather than a lookup language.

COUNT, SUM, AVG, MIN, MAX

The five functions that cover almost everything:

SELECT
    COUNT(*)      AS total_enrollments,
    COUNT(grade)  AS graded,
    ROUND(AVG(grade), 2) AS avg_grade,
    MIN(grade)    AS lowest,
    MAX(grade)    AS highest,
    SUM(grade)    AS total_points
FROM enrollments;

1 row

1 ms

Twenty rows in, one row out. That is the defining property of an aggregate: it consumes a set of rows and produces a single value.

COUNT(*) vs COUNT(col) vs COUNT(DISTINCT col)

Three functions that look almost identical and answer three different questions. This is asked in interviews constantly.

SELECT
    COUNT(*)             AS rows_total,      -- 10
    COUNT(city)          AS cities_present,  -- 9   (Sevara's NULL skipped)
    COUNT(DISTINCT city) AS unique_cities    -- 6   (NULL not counted at all)
FROM students;

1 row

0 ms

SELECT COUNT(*) AS rows,
       COUNT(*) - COUNT(city)   AS missing_city,
       COUNT(*) - COUNT(gender) AS missing_gender
FROM students;

How aggregates treat NULL

Every aggregate except COUNT(*) skips NULLs silently. Usually that is exactly right — but it changes the denominator, and students must know it.

Same data, two answers, both defensible — but they mean different things. Ask the business question first: "the average of the grades we have" (79.43) or "the average score per enrollment, where ungraded counts as nothing" (55.60)? The first is almost always what is wanted; the second is almost always a bug.

GROUP BY

GROUP BY takes the one-number-for-the-whole-table idea and applies it per group. Instead of "the average grade", you get "the average grade for each course".

SELECT
    course_id,
    COUNT(*)             AS enrollments,
    COUNT(grade)         AS graded,
    ROUND(AVG(grade), 2) AS avg_grade,
    MAX(grade)           AS best
FROM enrollments
GROUP BY course_id
ORDER BY course_id;

14 rows

1 ms

Grouping by a text column works the same way:

SELECT status, COUNT(*) AS n
FROM enrollments
GROUP BY status
ORDER BY n DESC;

4 rows

1 ms

And NULL forms its own group — the one place SQL treats all NULLs as equal:

SELECT COALESCE(city, '(unknown)') AS city, COUNT(*) AS students
FROM students
GROUP BY city
ORDER BY students DESC, city;
-- Tashkent 3, Samarkand 2, then Andijan/Bukhara/Fergana/Namangan/(unknown) 1 each

9 rows

0 ms

The golden rule of GROUP BY

The reason is simple once you look at the diagram above. After grouping, course 1 is one output row built from four input rows. Ask for student_id and the question has no answer — there were four different students. Which one should it print?

-- WRONG: student_id is neither grouped nor aggregated
SELECT course_id, student_id, AVG(grade)
FROM enrollments
GROUP BY course_id;

-- PostgreSQL: ERROR — column "student_id" must appear in the GROUP BY clause
-- SQLite/MySQL: NO error — they invent an arbitrary value. Far more dangerous.

14 rows

1 ms

The three legitimate ways to get what was probably wanted:

-- (a) group by it too — one row per (course, student)
SELECT course_id, student_id, AVG(grade) FROM enrollments
GROUP BY course_id, student_id;

-- (b) aggregate it — e.g. how many distinct students
SELECT course_id, COUNT(DISTINCT student_id) AS students, AVG(grade) FROM enrollments
GROUP BY course_id;

-- (c) you actually wanted "the top student per course" → window function, Chapter 7

Result 1

… first 15 of 1866 rows

1866 rows

Result 2

14 rows

3 ms

Grouping by several columns Listing several columns makes one group per combination. The number of output rows equals the number of combinations that actually occur — not every theoretical pairing.

-- "How many students of each gender, per city?"
SELECT
    COALESCE(city, '(unknown)') AS city,
    gender,
    COUNT(*) AS students
FROM students
GROUP BY city, gender
ORDER BY city, gender;

… first 15 of 18 rows

18 rows

0 ms

SELECT COALESCE(city,'(unknown)') AS city,
       SUM(CASE WHEN gender = 'M' THEN 1 ELSE 0 END) AS male,
       SUM(CASE WHEN gender = 'F' THEN 1 ELSE 0 END) AS female,
       COUNT(*) AS total
FROM students
GROUP BY city;

HAVING vs WHERE

The rule from Chapter 1, now with the reason visible: WHERE filters rows before grouping; HAVING filters groups after grouping.

-- "Among completed enrollments only, which courses average above 80?"
SELECT
    course_id,
    COUNT(*)             AS completed_count,
    ROUND(AVG(grade), 2) AS avg_grade
FROM enrollments
WHERE status = 'completed'      -- filters ROWS: drops active/failed/dropped
GROUP BY course_id
HAVING AVG(grade) > 80          -- filters GROUPS: drops low-average courses
ORDER BY avg_grade DESC;

1 row

1 ms

Course 4 is gone: its completed grades are 81 and 79, averaging exactly 80.0, and > 80 is strict. Course 3 averages 76.5. Note also that course 1's average jumped from 68.0 to 83.5 — because WHERE status = 'completed' removed the two failing grades first. Changing WHERE changes what the groups contain.

WHERE  status = 'completed'  GROUP BY course_id            -- fast
GROUP BY course_id           HAVING status = 'completed'   -- slow / invalid

SELECT student_id, course_id, COUNT(*) AS times
FROM enrollments
GROUP BY student_id, course_id
HAVING COUNT(*) > 1;

ROUND and the integer-division trap

The Chapter 2 trap comes back here, and in aggregation it does real damage.

-- "What percentage of enrollments are completed?"

-- WRONG: 12 / 20 in integer arithmetic = 0
SELECT COUNT(*) / (SELECT COUNT(*) FROM enrollments) * 100 AS pct
FROM enrollments WHERE status = 'completed';
-- returns 0

-- RIGHT: force a decimal with * 1.0 before dividing
SELECT ROUND(COUNT(*) * 100.0 / (SELECT COUNT(*) FROM enrollments), 1) AS pct
FROM enrollments WHERE status = 'completed';
-- returns 60.0

Result 1

1 row

Result 2

1 row

1 ms

The compact idiom for "percentage of rows matching a condition", worth memorising:

-- pass rate per course, in one query
SELECT
    course_id,
    COUNT(*) AS graded,
    ROUND(100.0 * SUM(CASE WHEN grade >= 60 THEN 1 ELSE 0 END) / COUNT(*), 1) AS pass_rate_pct
FROM enrollments
WHERE grade IS NOT NULL
GROUP BY course_id
ORDER BY pass_rate_pct DESC;

14 rows

1 ms

Note the WHERE grade IS NOT NULL: without it, ungraded enrollments would sit in the denominator and drag every pass rate down. Deciding what belongs in the denominator is the actual analytical work — the SQL is the easy part.

Practice set

-- 1. How many students in each city? (unknown shown explicitly)
SELECT COALESCE(city, '(unknown)') AS city, COUNT(*) AS n
FROM students GROUP BY city ORDER BY n DESC;

-- 2. Average price of courses per category
SELECT category, COUNT(*) AS courses, ROUND(AVG(price)) AS avg_price
FROM courses GROUP BY category ORDER BY avg_price DESC;

-- 3. Each student's average grade, best students first
SELECT student_id,
       COUNT(grade)         AS graded,
       ROUND(AVG(grade), 1) AS avg_grade
FROM enrollments
GROUP BY student_id
HAVING COUNT(grade) > 0
ORDER BY avg_grade DESC;

-- 4. Courses with more than 3 enrollments
SELECT course_id, COUNT(*) AS n
FROM enrollments GROUP BY course_id HAVING COUNT(*) > 3;

-- 5. Monthly enrollment counts
SELECT strftime('%Y-%m', enrolled_at) AS month, COUNT(*) AS n
FROM enrollments GROUP BY month ORDER BY month;

-- 6. Completion rate per course
SELECT course_id,
       COUNT(*) AS total,
       SUM(CASE WHEN status = 'completed' THEN 1 ELSE 0 END) AS completed,
       ROUND(100.0 * SUM(CASE WHEN status = 'completed' THEN 1 ELSE 0 END) / COUNT(*), 1) AS pct
FROM enrollments GROUP BY course_id ORDER BY pct DESC;

-- 7. Data-quality check: NULLs per column
SELECT COUNT(*) AS rows_total,
       COUNT(*) - COUNT(city)       AS missing_city,
       COUNT(*) - COUNT(birth_date) AS missing_birth_date
FROM students;

-- 8. Any duplicate enrollments? (should return nothing)
SELECT student_id, course_id, COUNT(*) AS times
FROM enrollments GROUP BY student_id, course_id HAVING COUNT(*) > 1;

Result 1

9 rows

Result 2

5 rows

Result 3

… first 15 of 300 rows

300 rows

Result 4

14 rows

Result 5

4 rows

Result 6

14 rows

Result 7

1 row

Result 8

… first 15 of 514 rows

514 rows

5 ms

Interview questions

Q: COUNT(*) vs COUNT(column) vs COUNT(1)?

COUNT(*) counts rows, including rows that are entirely NULL. COUNT(column) counts only rows where that column is not NULL. COUNT(1) is identical to COUNT(*) in every modern engine — the old belief that it is faster is a myth.

Q: Does AVG include NULLs?

No. It divides by the count of non-NULL values. If you want NULLs treated as zero you must say so: AVG(COALESCE(grade, 0)) — but ask whether that is genuinely what the business means.

Q: What does SUM return over zero rows?

NULL, not 0. Wrap it in COALESCE(SUM(x), 0) when a number is required. COUNT returns 0 correctly.

Q: WHERE vs HAVING?

WHERE filters rows before GROUP BY and cannot use aggregates. HAVING filters groups after GROUP BY and can. Any condition that does not need an aggregate belongs in WHERE, because filtering earlier means less work.

Q: Can you use HAVING without GROUP BY?

Yes. The whole table is then treated as one group: SELECT AVG(grade) FROM enrollments HAVING AVG(grade) > 70; returns one row or none.

Q: Why is SELECT course_id, student_id, AVG(grade) ... GROUP BY course_id wrong?

Because a group holds many different student_id values and only one can be printed. PostgreSQL raises an error; SQLite and MySQL return an arbitrary value, which is worse — the result looks valid but is meaningless.

Q: How do you find duplicate rows?

GROUP BY the columns that should be unique, then HAVING COUNT(*) > 1.

Q: Why might a "sales by product" report be missing products?

Because GROUP BY only sees rows that exist. A product with zero sales has no rows to group. You need a LEFT JOIN from the products table to keep it in the report with a count of 0.

Summary

• Aggregates collapse many rows into one: COUNT, SUM, AVG, MIN, MAX.
• COUNT(*) = rows · COUNT(col) = non-NULL values · COUNT(DISTINCT col) = distinct non-NULL values. Their difference is a free NULL-check.
• Every aggregate except COUNT(*) skips NULLs, which changes the denominator of AVG. SUM over zero rows is NULL, not 0.
• GROUP BY makes one output row per group. It cannot show groups that have no rows — that needs a LEFT JOIN.
• Golden rule: every selected column is either in GROUP BY or inside an aggregate. SQLite/MySQL let you break it; do not.
• WHERE before grouping, HAVING after. Filter as early as possible.
• GROUP BY ... HAVING COUNT(*) > 1 is the standard duplicate finder.
• Force decimals with * 1.0 or * 100.0; SUM(CASE WHEN ... THEN 1 ELSE 0 END) is the percentage idiom.
• Always report COUNT alongside AVG, so nobody trusts an average built from one value.

Next: Chapter 5 — JOINs. Every report so far has been trapped inside a single table; JOINs are what let you say "student name and course title" in the same result.