SQL Fundamentals
Aggregation and Grouping
An aggregate collapses many rows into one value.
JrCodex·7 min read
Jr Codex DBMS Notes
Level: Intermediate Prerequisites: Chapter 3: Joins Time to complete: ~25 minutes
Table of Contents
- Aggregate Functions
- How Aggregates Treat NULL
- GROUP BY
- HAVING
- Grouping Across Joins
- Window Functions
- Summary & Next Steps
1. Aggregate Functions
An aggregate collapses many rows into one value.
SELECT COUNT(*) AS students,
AVG(marks) AS average,
MIN(marks) AS lowest,
MAX(marks) AS highest,
SUM(marks) AS total
FROM students;| Function | Returns |
|---|---|
COUNT(*) | Number of rows |
COUNT(col) | Number of non-NULL values in col |
COUNT(DISTINCT col) | Number of distinct non-NULL values |
SUM, AVG | Only meaningful on numerics |
MIN, MAX | Work on numbers, text and dates |
STRING_AGG / GROUP_CONCAT | Concatenates values into one string |
2. How Aggregates Treat NULL
This single behaviour causes more wrong numbers than anything else in SQL.
The Rule
─────────────────────────────────────────
Every aggregate except COUNT(*) IGNORES NULLs.
Our six students, one with NULL marks:
COUNT(*) = 6 counts ROWS
COUNT(marks) = 5 skips Sam
SUM(marks) = 400 skips Sam
AVG(marks) = 80 = 400 / 5, NOT / 6
─────────────────────────────────────────
Why This Matters
─────────────────────────────────────────
AVG divides by the count of NON-NULL values.
If "not yet assessed" should count as zero, AVG
gives you the wrong answer and looks completely
reasonable doing it.
Decide explicitly which you want:
AVG(marks) average of those
assessed
AVG(COALESCE(marks, 0)) treats unassessed
as zero
SUM(marks)/COUNT(*) same, written
differently
─────────────────────────────────────────
SELECT COUNT(*) AS all_students, -- 6
COUNT(marks) AS assessed, -- 5
COUNT(*) - COUNT(marks) AS not_assessed, -- 1
ROUND(AVG(marks), 1) AS avg_of_assessed, -- 80.0
ROUND(AVG(COALESCE(marks,0)),1) AS avg_treating_null_as_zero -- 66.7
FROM students;The gap between 80.0 and 66.7 is the whole point. Both are defensible; only one answers your actual question.
3. GROUP BY
GROUP BY splits rows into groups and applies the aggregate to each.
SELECT city,
COUNT(*) AS students,
ROUND(AVG(marks), 1) AS avg_marks
FROM students
GROUP BY city
ORDER BY avg_marks DESC;Result
─────────────────────────────────────────
Pune | 3 | 89.5 ← 3 students, but AVG over
the 2 with marks
Delhi | 2 | 78.0
Mumbai | 1 | 65.0
─────────────────────────────────────────
The Golden Rule
─────────────────────────────────────────
Every column in SELECT must either
(a) appear in GROUP BY, or
(b) be inside an aggregate function.
WHY: the group is many rows. If you ask for
`name` alongside a group of three students,
which name should it return? The question has no
answer, so it is an error.
MySQL historically allowed it and returned an
arbitrary value — a source of silently wrong
reports. Modern versions reject it by default.
─────────────────────────────────────────
-- Grouping by several columns: one group per DISTINCT COMBINATION.
SELECT city,
CASE WHEN marks >= 80 THEN 'high' ELSE 'low' END AS band,
COUNT(*) AS n
FROM students
WHERE marks IS NOT NULL
GROUP BY city, CASE WHEN marks >= 80 THEN 'high' ELSE 'low' END
ORDER BY city, band;4. HAVING
WHERE filters rows before grouping; HAVING filters groups after.
SELECT city, COUNT(*) AS students, ROUND(AVG(marks),1) AS avg_marks
FROM students
WHERE marks IS NOT NULL -- filters ROWS (step 2)
GROUP BY city -- forms GROUPS (step 3)
HAVING COUNT(*) >= 2 -- filters GROUPS (step 4)
ORDER BY avg_marks DESC;The Distinction, Concretely
─────────────────────────────────────────
WHERE marks IS NOT NULL
removes Sam before any grouping happens.
Pune's group now has 2 members.
HAVING COUNT(*) >= 2
removes Mumbai, whose group has 1 member.
It could not be written in WHERE, because
COUNT(*) does not exist until groups do.
─────────────────────────────────────────
The Performance Rule
─────────────────────────────────────────
If a condition CAN go in WHERE, put it there.
WHERE discards rows before the sort or hash that
grouping requires. HAVING discards after all that
work is done.
On a large table the difference is substantial,
and it is free.
─────────────────────────────────────────
5. Grouping Across Joins
-- How many students per course, and the average grade points.
SELECT c.title,
c.dept,
COUNT(e.student_id) AS enrolled, -- COUNT a COLUMN, not *
COUNT(e.grade) AS graded
FROM courses c
LEFT JOIN enrollments e ON c.id = e.course_id -- LEFT: keep courses with nobody
GROUP BY c.id, c.title, c.dept
ORDER BY enrolled DESC;Two Deliberate Choices
─────────────────────────────────────────
COUNT(e.student_id) not COUNT(*)
In a LEFT JOIN, an unmatched course gets one
NULL-padded row. COUNT(*) would report 1
enrolled; counting the column reports 0
(Chapter 3, mistake 4).
GROUP BY c.id, c.title, c.dept
Group by the KEY plus everything selected.
Grouping by title alone would merge two
courses that happened to share a title.
─────────────────────────────────────────
-- The fan-out trap from Chapter 3, and its fix.
-- WRONG: each student's marks counted once per enrollment.
SELECT s.city, SUM(s.marks)
FROM students s JOIN enrollments e ON s.id = e.student_id
GROUP BY s.city;
-- RIGHT: aggregate the many-side first, then join.
SELECT s.city, SUM(s.marks) AS total_marks, SUM(x.n) AS total_enrollments
FROM students s
LEFT JOIN (SELECT student_id, COUNT(*) AS n FROM enrollments GROUP BY student_id) x
ON x.student_id = s.id
GROUP BY s.city;6. Window Functions
Aggregates collapse rows. Window functions compute across a set of rows while keeping every row.
SELECT name,
city,
marks,
AVG(marks) OVER (PARTITION BY city) AS city_avg,
marks - AVG(marks) OVER (PARTITION BY city) AS vs_city_avg,
RANK() OVER (ORDER BY marks DESC) AS overall_rank
FROM students
WHERE marks IS NOT NULL;The Difference, Stated Once
─────────────────────────────────────────
GROUP BY 6 rows ──► 3 rows (one per city)
You lose the individual students.
OVER 6 rows ──► 6 rows
Each row gains its city's average
alongside its own value.
If you ever wrote a query, then joined it back to
the original table to attach a group statistic to
each row — that is what OVER does in one step.
─────────────────────────────────────────
-- The common ranking-within-group pattern.
SELECT * FROM (
SELECT name, city, marks,
ROW_NUMBER() OVER (PARTITION BY city ORDER BY marks DESC) AS rn
FROM students WHERE marks IS NOT NULL
) ranked
WHERE rn = 1; -- top student in each cityThree Ranking Functions
─────────────────────────────────────────
ROW_NUMBER() 1,2,3,4 always distinct; ties
broken arbitrarily
RANK() 1,2,2,4 ties share a rank, then
it SKIPS
DENSE_RANK() 1,2,2,3 ties share, no gap
Use ROW_NUMBER for "pick exactly one per group",
RANK for leaderboards where ties genuinely tie.
─────────────────────────────────────────
7. Summary & Next Steps
Key Takeaways
- Every aggregate except
COUNT(*)ignores NULLs, soAVGdivides by the non-NULL count — decide deliberately whether missing values should count as zero. - Every selected column must be grouped or aggregated, because a group of many rows has no single value for an ungrouped column.
WHEREfilters rows before grouping andHAVINGfilters groups after; put a condition inWHEREwhenever it can go there.- Window functions compute group statistics while keeping every row, replacing the aggregate-then-join-back pattern with one clause.
Concept Check
AVG(marks)returns 80 andAVG(COALESCE(marks,0))returns 66.7 on the same table. Explain the difference and when each is right.- Why is
COUNT(*)wrong when counting enrollments in aLEFT JOINfrom courses? - When would you use
ROW_NUMBER()rather thanRANK()?
Next Chapter
→ Chapter 5: Subqueries and CTEs
Jr Codex — 1-on-1 Personalized Coaching | Back to Module Index | Back to DBMS Index