Lesson 16: Aggregation, GROUP BY and HAVING¶
Summarising data — counts, totals and per-group filtering
About this lesson
Course: Database Management Systems
Unit 4: Structured Query Language · 12 Lecture Hours
Lesson duration: 2 lectures
Reading: Elmasri & Navathe, Ch. 7.1 · Murach's MySQL, Ch. 6
Learning Outcomes
- Use
COUNT,SUM,AVG,MAX,MINcorrectly, including their treatment of NULL. - Distinguish
COUNT(*),COUNT(column)andCOUNT(DISTINCT column). - Write
GROUP BYqueries and state the single-value rule. - Distinguish
WHEREfromHAVINGprecisely. - Combine grouping with joins, including the
LEFT JOINidiom for counting zeros.
16.1 Aggregate Functions¶
Aggregate function
A function that takes a set of values and returns a single value. Aggregates collapse many rows into one.
| Function | Returns | Works on |
|---|---|---|
COUNT(*) |
Number of rows | Any |
COUNT(col) |
Number of non-NULL values | Any |
COUNT(DISTINCT col) |
Number of distinct non-NULL values | Any |
SUM(col) |
Total | Numeric only |
AVG(col) |
Arithmetic mean | Numeric only |
MAX(col) |
Largest | Numeric, string, date |
MIN(col) |
Smallest | Numeric, string, date |
SELECT COUNT(*) AS TotalCourses,
SUM(TotalSeats) AS AllSeats,
AVG(TotalSeats) AS MeanSeats,
MAX(TotalSeats) AS Largest,
MIN(TotalSeats) AS Smallest
FROM COURSE;
| TotalCourses | AllSeats | MeanSeats | Largest | Smallest |
|---|---|---|---|---|
| 5 | 235 | 47.0000 | 60 | 40 |
With no GROUP BY, the whole table is one group and exactly one row is returned.
The three forms of COUNT¶
COUNT(*) vs COUNT(col) vs COUNT(DISTINCT col)
Suppose ADMISSION holds 6 rows, involving 4 distinct students, and a nullable column Grade has 2 NULLs.
| Expression | Result | Counts |
|---|---|---|
COUNT(*) |
6 | Rows — NULLs included |
COUNT(Rollno) |
6 | Non-NULL Rollno values (it is NOT NULL, so all) |
COUNT(DISTINCT Rollno) |
4 | Distinct students |
COUNT(Grade) |
4 | Non-NULL grades only |
COUNT(*) is the only aggregate that does not ignore NULLs. Use it when you mean "how many rows", and COUNT(col) when you mean "how many rows have a value here".
Aggregates and NULL¶
All aggregates except COUNT(*) ignore NULLs entirely. This matters most for AVG:
-- Marks: 80, 90, NULL, NULL
SELECT AVG(Marks) FROM RESULT; -- 85 = (80+90)/2
SELECT SUM(Marks)/COUNT(*) FROM RESULT; -- 42.5 = 170/4
SELECT AVG(COALESCE(Marks,0)) FROM RESULT; -- 42.5 -- NULL treated as zero
All three are defensible; they answer different questions. AVG(Marks) is the average of the marks that exist; the others treat a missing mark as zero. Decide which you mean, and be explicit.
16.2 GROUP BY¶
GROUP BY
Partitions the rows into groups that share the same values in the grouping columns, then applies each aggregate function once per group, producing one output row per group.
SELECT Coursetype, COUNT(*) AS NumCourses, AVG(TotalSeats) AS MeanSeats
FROM COURSE
GROUP BY Coursetype;
| Coursetype | NumCourses | MeanSeats |
|---|---|---|
| Fulltime | 3 | 48.3333 |
| Parttime | 2 | 45.0000 |
BEFORE GROUPING (5 rows) AFTER GROUP BY Coursetype (2 rows)
CS01 Fulltime 60 --,
CH01 Fulltime 45 --+--> group 'Fulltime' --> COUNT=3 AVG=48.33
PH01 Fulltime 40 --'
MA02 Parttime 50 --,
EN03 Parttime 40 --+--> group 'Parttime' --> COUNT=2 AVG=45.00
The single-value rule¶
The rule that governs every GROUP BY query
Every column in the SELECT list must either appear in the GROUP BY clause, or be inside an aggregate function.
The reason is arithmetic: a group produces one output row, so every expression in that row must have one value for the group. A grouping column has one value by construction; an aggregate collapses to one value by definition. Any other column has many values in the group, and the DBMS cannot choose among them.
-- INVALID: which of the three Fulltime course names should be shown?
SELECT Coursetype, Cname, COUNT(*) FROM COURSE GROUP BY Coursetype;
-- VALID
SELECT Coursetype, COUNT(*) FROM COURSE GROUP BY Coursetype;
SELECT Coursetype, MAX(Cname), COUNT(*) FROM COURSE GROUP BY Coursetype;
SELECT Coursetype, Cname, COUNT(*) FROM COURSE GROUP BY Coursetype, Cname;
A MySQL warning. Older MySQL versions, and installations without ONLY_FULL_GROUP_BY in the SQL mode, accept the invalid form and return an arbitrary value from the group — silently wrong. Standard SQL and modern MySQL reject it. Write standard-conforming SQL; do not rely on the permissive behaviour.
Grouping by several columns¶
SELECT c.Coursetype, YEAR(a.Dateofadmission) AS Yr, COUNT(*) AS Admissions
FROM ADMISSION a JOIN COURSE c ON a.SID = c.SID
GROUP BY c.Coursetype, YEAR(a.Dateofadmission)
ORDER BY c.Coursetype, Yr;
One group per distinct combination of the grouping columns.
16.3 HAVING¶
HAVING
Filters groups after they are formed, exactly as WHERE filters rows before grouping. HAVING may reference aggregate functions; WHERE may not.
Course Q8 — course names with more than five students enrolled:
SELECT c.Cname, COUNT(*) AS Enrolled
FROM ADMISSION a JOIN COURSE c ON a.SID = c.SID
GROUP BY c.SID, c.Cname
HAVING COUNT(*) > 5;
WHERE vs HAVING — the definitive comparison
WHERE |
HAVING |
|
|---|---|---|
| Filters | Individual rows | Groups |
| Evaluated | Before GROUP BY (step 2) |
After GROUP BY (step 4) |
| May use aggregates | No | Yes |
| May use non-grouped columns | Yes | No (same single-value rule) |
Without GROUP BY |
Normal row filter | Treats the whole table as one group |
| Performance | Better — reduces rows before grouping | Necessary only for aggregate conditions |
Rule of thumb: if the condition can be tested on a single row, put it in WHERE. Only conditions on an aggregate belong in HAVING.
Both in one query:
-- Full-time courses (row filter) having more than 5 students (group filter)
SELECT c.Cname, COUNT(*) AS Enrolled
FROM ADMISSION a JOIN COURSE c ON a.SID = c.SID
WHERE c.Coursetype = 'Fulltime' -- filter rows first
GROUP BY c.SID, c.Cname
HAVING COUNT(*) > 5 -- then filter groups
ORDER BY Enrolled DESC;
Why WHERE first is faster
Putting Coursetype = 'Fulltime' in WHERE discards part-time rows before the grouping work begins — fewer rows to sort or hash into groups.
Writing it as HAVING MAX(c.Coursetype) = 'Fulltime' would group everything first and then throw groups away. Same answer, more work. This is the SQL counterpart of the push selections down rule from Unit 3, Lesson 3.
16.4 The LEFT JOIN Idiom for Counting Zeros¶
This is the single most important practical pattern in this lesson.
-- WRONG: courses with no students simply vanish
SELECT c.Cname, COUNT(*) AS Enrolled
FROM COURSE c JOIN ADMISSION a ON c.SID = a.SID
GROUP BY c.SID, c.Cname;
An inner join produces no row for PH01 Physics, so it forms no group and never appears — the report silently understates the course list.
-- RIGHT
SELECT c.Cname, COUNT(a.Rollno) AS Enrolled
FROM COURSE c LEFT JOIN ADMISSION a ON c.SID = a.SID
GROUP BY c.SID, c.Cname
ORDER BY Enrolled DESC;
| Cname | Enrolled |
|---|---|
| Computer Science | 2 |
| Mathematics | 2 |
| Chemistry | 1 |
| English | 1 |
| Physics | 0 |
Two details that must both be right
LEFT JOINkeeps every course, padding unmatched ones with NULLs.COUNT(a.Rollno), notCOUNT(*). ForPH01the left join produces one row with allADMISSIONcolumns NULL.COUNT(*)would count that row and report 1;COUNT(a.Rollno)ignores the NULL and correctly reports 0.
Getting the join right but leaving COUNT(*) is the classic half-correct answer.
Course Q17 — display the vacant seats for each course:
SELECT c.Cname, c.TotalSeats,
COUNT(a.Rollno) AS Filled,
c.TotalSeats - COUNT(a.Rollno) AS Vacant
FROM COURSE c LEFT JOIN ADMISSION a ON c.SID = a.SID
GROUP BY c.SID, c.Cname, c.TotalSeats
ORDER BY Vacant DESC;
Course Q22 — count courses with more than five students, for each course type:
SELECT Coursetype, COUNT(*) AS PopularCourses
FROM ( SELECT c.SID, c.Coursetype
FROM COURSE c JOIN ADMISSION a ON c.SID = a.SID
GROUP BY c.SID, c.Coursetype
HAVING COUNT(*) > 5 ) AS popular
GROUP BY Coursetype;
Two levels of grouping are needed: the inner query finds which courses qualify, the outer counts them per type. This nested aggregation pattern is common whenever a question aggregates over the result of an aggregation.
Course Q16 — courses where enrolment is only 10 per cent of total seats:
SELECT c.Cname, c.TotalSeats, COUNT(a.Rollno) AS Enrolled
FROM COURSE c LEFT JOIN ADMISSION a ON c.SID = a.SID
GROUP BY c.SID, c.Cname, c.TotalSeats
HAVING COUNT(a.Rollno) = c.TotalSeats * 0.10;
16.5 Solved Questions¶
Solved Question 1
Q. Distinguish COUNT(*), COUNT(col) and COUNT(DISTINCT col) with a worked example. Which ignores NULLs?
A.
COUNT(*)counts rows, regardless of content. It is the only aggregate that does not ignore NULLs.COUNT(col)counts rows wherecolis not NULL.COUNT(DISTINCT col)counts distinct non-NULL values ofcol.
Take a table RESULT(Rollno, SID, Grade):
| Rollno | SID | Grade |
|---|---|---|
| X2019 | CS01 | A |
| X2019 | MA02 | B |
| X2020 | CS01 | A |
| Z1188 | CH01 | NULL |
| Z1188 | EN03 | NULL |
| Expression | Result | Reason |
|---|---|---|
COUNT(*) |
5 | Five rows. |
COUNT(Grade) |
3 | Two grades are NULL and are ignored. |
COUNT(DISTINCT Grade) |
2 | Distinct non-NULL grades are A and B. |
COUNT(Rollno) |
5 | Rollno is NOT NULL. |
COUNT(DISTINCT Rollno) |
3 | Three distinct students. |
Practical significance. "How many students have results?" is COUNT(DISTINCT Rollno) = 3, not COUNT(*) = 5. "How many results have been entered?" is COUNT(Grade) = 3, not COUNT(*) = 5. Choosing the wrong form gives a plausible but wrong number, which is worse than an error.
Solved Question 2
Q. State and justify the single-value rule for GROUP BY. Give an invalid query and three ways to fix it.
A. The rule: every column in the SELECT list must either appear in the GROUP BY clause or be enclosed in an aggregate function.
Justification. GROUP BY produces exactly one output row per group. Every expression in that row must therefore evaluate to exactly one value for the group. A grouping column has one value by construction — that is what defines the group. An aggregate reduces many values to one by definition. Any other column holds several values within the group, and there is no principled way to pick one.
Invalid:
SELECT Coursetype, Cname, COUNT(*) FROM COURSE GROUP BY Coursetype;
Fulltime group contains three different Cname values — Computer Science, Chemistry, Physics — so the query does not specify which to display.
Three fixes, answering three different questions:
-
Drop the offending column — "how many courses of each type?"
SELECT Coursetype, COUNT(*) FROM COURSE GROUP BY Coursetype; -
Aggregate it — "how many, and one representative name?"
SELECT Coursetype, MAX(Cname) AS SampleCourse, COUNT(*) FROM COURSE GROUP BY Coursetype; -
Add it to
GROUP BY— "how many courses share each (type, name) pair?" This makes the groups finer, so the answer changes.SELECT Coursetype, Cname, COUNT(*) FROM COURSE GROUP BY Coursetype, Cname;
Caution. MySQL without ONLY_FULL_GROUP_BY accepts the invalid query and returns an arbitrary Cname — no error, wrong answer. Always write the standard-conforming form.
Solved Question 3
Q. Distinguish WHERE from HAVING on at least five points. Write one query using both, and explain why the split is as it is.
A. (Comparison table of Section 4.3.) The essential differences: WHERE filters rows before grouping and cannot reference aggregates; HAVING filters groups after grouping and can.
SELECT c.Cname, COUNT(*) AS Enrolled
FROM ADMISSION a JOIN COURSE c ON a.SID = c.SID
WHERE c.Coursetype = 'Fulltime'
GROUP BY c.SID, c.Cname
HAVING COUNT(*) > 5
ORDER BY Enrolled DESC;
"Among full-time courses, list those with more than five students."
Why the split is as it is. Coursetype = 'Fulltime' is testable on a single row — each admission row, joined to its course, either is or is not full-time. It therefore belongs in WHERE, and putting it there discards part-time rows before the grouping work begins.
COUNT(*) > 5 is not testable on a single row: it is a property of the whole group, which does not exist until GROUP BY has run. It therefore must go in HAVING.
Performance. Moving the Coursetype test into HAVING (as HAVING MAX(c.Coursetype)='Fulltime') would produce the same answer while grouping every row first and then discarding whole groups — strictly more work. This is the SQL form of the push selections down optimisation rule.
Solved Question 4
Q. Write a query listing every course with its enrolment count, including courses with none. Explain both details that must be right.
A.
SELECT c.Cname, COUNT(a.Rollno) AS Enrolled
FROM COURSE c
LEFT JOIN ADMISSION a ON c.SID = a.SID
GROUP BY c.SID, c.Cname
ORDER BY Enrolled DESC, c.Cname;
Detail 1 — the join must be LEFT JOIN. An inner join emits a row only where a match exists. PH01 Physics has no admissions, so an inner join produces no row for it, it forms no group, and it is silently absent from the report. A LEFT JOIN retains every course, padding the ADMISSION columns with NULLs where there is no match.
Detail 2 — the aggregate must be COUNT(a.Rollno), not COUNT(*). After the left join, PH01 contributes exactly one row, whose ADMISSION columns are all NULL. COUNT(*) counts rows and would report 1 — a wrong answer that looks entirely plausible. COUNT(a.Rollno) ignores NULLs and correctly reports 0.
Result:
| Cname | Enrolled |
|---|---|
| Computer Science | 2 |
| Mathematics | 2 |
| Chemistry | 1 |
| English | 1 |
| Physics | 0 |
Grouping by c.SID as well as c.Cname is deliberate: SID is the key, so two courses that happened to share a name would still be separated. Both are in the GROUP BY, satisfying the single-value rule.
Solved Question 5
Q. Write SQL for course practicals 6, 8, 13, 17 and 22.
A.
Q6 — course details with more than N students enrolled, N supplied by the user.
SET @N = 5;
SELECT c.*, COUNT(a.Rollno) AS Enrolled
FROM COURSE c LEFT JOIN ADMISSION a ON c.SID = a.SID
GROUP BY c.SID
HAVING COUNT(a.Rollno) > @N;
?), which is also the defence against SQL injection.
Q8 — course names in which more than five students have enrolled.
SELECT c.Cname, COUNT(*) AS Enrolled
FROM ADMISSION a JOIN COURSE c ON a.SID = c.SID
GROUP BY c.SID, c.Cname
HAVING COUNT(*) > 5;
> 5, so nothing is lost by excluding it.
Q13 — course names in which more than 30 students took admission.
SELECT c.Cname, COUNT(*) AS Admitted
FROM ADMISSION a JOIN COURSE c ON a.SID = c.SID
GROUP BY c.SID, c.Cname
HAVING COUNT(*) > 30;
Q17 — display the vacant seats for each course.
SELECT c.Cname, c.TotalSeats,
COUNT(a.Rollno) AS Filled,
c.TotalSeats - COUNT(a.Rollno) AS Vacant
FROM COURSE c LEFT JOIN ADMISSION a ON c.SID = a.SID
GROUP BY c.SID, c.Cname, c.TotalSeats
ORDER BY Vacant DESC;
LEFT JOIN is essential: a course with no students has all its seats vacant and must appear, with Vacant = TotalSeats.
Q22 — number of courses with more than five students, per course type.
SELECT Coursetype, COUNT(*) AS PopularCourses
FROM ( SELECT c.SID, c.Coursetype
FROM COURSE c JOIN ADMISSION a ON c.SID = a.SID
GROUP BY c.SID, c.Coursetype
HAVING COUNT(*) > 5 ) AS popular
GROUP BY Coursetype;
GROUP BY cannot do it, because the two aggregations are at different granularities.
Solved Question 6
Q. AVG(Marks) returns 85 but SUM(Marks)/COUNT(*) returns 42.5 on the same data. Explain, and say which is correct.
A. The data must contain NULLs. Suppose four rows with marks 80, 90, NULL, NULL.
AVG(Marks)ignores NULLs: it averages only the two present values,(80+90)/2 = 85.SUM(Marks)also ignores NULLs, giving 170; butCOUNT(*)counts rows, giving 4. So170/4 = 42.5.
The mismatch arises because SUM and COUNT(*) disagree about the denominator: SUM skipped the NULLs, COUNT(*) did not.
Which is correct depends on what the NULLs mean — the three meanings from Unit 2, Lesson 2:
- If NULL means "the mark has not yet been entered" (unknown-missing), then those students should not influence the class average, and
AVG(Marks)= 85 is correct. It is the average of the marks that exist. - If NULL means "the student was absent and scores zero", then the missing values are really zeros, and the correct expression states that explicitly:
SELECT AVG(COALESCE(Marks,0)) FROM RESULT; -- 42.5
The lesson. Both numbers are arithmetically right; they answer different questions. Never write SUM(x)/COUNT(*) intending an average — use AVG(x) if NULLs should be excluded, or AVG(COALESCE(x,0)) if they should count as zero. And always report the denominator alongside the average, so the reader can see which convention was used:
SELECT COUNT(*) AS Students, COUNT(Marks) AS Graded, AVG(Marks) AS MeanOfGraded
FROM RESULT;
16.6 Exercises¶
A. Short answer
- Name the five aggregate functions.
- Which aggregate does not ignore NULLs?
- State the single-value rule.
- Give two differences between
WHEREandHAVING. - Why is
COUNT(*)wrong after aLEFT JOINwhen counting matches? - What does a
GROUP BYquery with no matching rows return?
B. Write the SQL
- Total number of students.
- Number of distinct courses that have at least one admission.
- Average, maximum and minimum seats per course type.
- Number of admissions per month of 2019.
- Courses with more than two admissions, most popular first.
- For each student, the number of courses they are admitted to.
- Students admitted to more than one course.
- Each society with its member count, including empty societies.
- The society with the most members (course Q10).
- The two most popular part-time courses (course Q11).
- Course types where the average seat count exceeds 45.
- For each teacher, the number of courses and total seats they are responsible for.
- Percentage of total college seats represented by each course.
- Courses that are more than 90 per cent full.
C. Predict and explain
RESULT(Rollno, Marks)has 6 rows with marks 70, 80, NULL, 90, NULL, 60. Give: a.COUNT(*)b.COUNT(Marks)c.SUM(Marks)d.AVG(Marks)e.SUM(Marks)/COUNT(*)f.MAX(Marks)g.COUNT(DISTINCT Marks)- Explain why each is invalid, and correct it:
a.
SELECT Cname, COUNT(*) FROM COURSE;b.SELECT Coursetype, Cname FROM COURSE GROUP BY Coursetype;c.SELECT Cname FROM COURSE WHERE COUNT(*) > 1;d.SELECT Coursetype, COUNT(*) AS n FROM COURSE GROUP BY Coursetype HAVING n > 1;(consider portability) - A report of courses and enrolments shows 4 courses instead of 5. Diagnose and give two fixes.
D. Analytical
- Explain why placing a non-aggregate condition in
HAVINGrather thanWHEREgives the same answer but is slower. Illustrate with row counts. - Write two queries answering "the average number of students per course", one counting empty courses and one not. Explain when each is appropriate.
- Explain what MySQL does with an invalid
GROUP BYwhenONLY_FULL_GROUP_BYis off, and why this is dangerous. - Design a query producing a single row with: total students, total courses, total admissions, average admissions per student, and the number of students with no admission.
- Explain why the course Q11 query ("two most popular part-time courses") could give a misleading answer with ties, and rewrite it to handle them.
E. Objective — True or False with reason
AVG(col)equalsSUM(col)/COUNT(*).HAVINGcan be used withoutGROUP BY.COUNT(DISTINCT col)includes NULL as a distinct value.- Every column in
SELECTmust appear inGROUP BY. WHEREcan filter onCOUNT(*).- A
LEFT JOINwithCOUNT(*)correctly reports zero for unmatched rows.
Answers to Section E
- False.
AVGdivides by the number of non-NULL values;COUNT(*)counts all rows. They agree only when the column has no NULLs. - True. Without
GROUP BY, the whole table is one group, soHAVING COUNT(*) > 100is a valid filter on that single group. - False. It counts distinct non-NULL values only.
- False. It must appear in
GROUP BYor be inside an aggregate function. - False.
WHEREis evaluated before groups exist; aggregate conditions belong inHAVING. - False.
COUNT(*)counts the NULL-padded row and reports 1. UseCOUNT(<column from the right table>).
Lesson Summary
- Aggregates collapse a set of values to one. All ignore NULLs except
COUNT(*). COUNT(*)counts rows;COUNT(col)counts non-NULL values;COUNT(DISTINCT col)counts distinct non-NULL values.GROUP BYgives one row per group, so the single-value rule applies: every selected column must be grouped or aggregated.WHEREfilters rows before grouping and cannot see aggregates;HAVINGfilters groups after grouping and can. Put a condition inWHEREwhenever it is testable on one row.- To count zeros, use
LEFT JOINandCOUNT(<right-table column>)— both details are required.