Skip to content

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

  1. Use COUNT, SUM, AVG, MAX, MIN correctly, including their treatment of NULL.
  2. Distinguish COUNT(*), COUNT(column) and COUNT(DISTINCT column).
  3. Write GROUP BY queries and state the single-value rule.
  4. Distinguish WHERE from HAVING precisely.
  5. Combine grouping with joins, including the LEFT JOIN idiom 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

  1. LEFT JOIN keeps every course, padding unmatched ones with NULLs.
  2. COUNT(a.Rollno), not COUNT(*). For PH01 the left join produces one row with all ADMISSION columns 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 where col is not NULL.
  • COUNT(DISTINCT col) counts distinct non-NULL values of col.

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;
The 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:

  1. Drop the offending column — "how many courses of each type?"

    SELECT Coursetype, COUNT(*) FROM COURSE GROUP BY Coursetype;
    

  2. Aggregate it — "how many, and one representative name?"

    SELECT Coursetype, MAX(Cname) AS SampleCourse, COUNT(*) FROM COURSE GROUP BY Coursetype;
    

  3. 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;
A user variable stands in for the input; in an application this becomes a bound parameter (?), 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;
An inner join suffices here: a course with zero students can never satisfy > 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;
Here the 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;
This needs two levels of aggregation: the inner query determines which courses qualify (one row per qualifying course), and the outer counts those rows per type. A single 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; but COUNT(*) counts rows, giving 4. So 170/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

  1. Name the five aggregate functions.
  2. Which aggregate does not ignore NULLs?
  3. State the single-value rule.
  4. Give two differences between WHERE and HAVING.
  5. Why is COUNT(*) wrong after a LEFT JOIN when counting matches?
  6. What does a GROUP BY query with no matching rows return?

B. Write the SQL

  1. Total number of students.
  2. Number of distinct courses that have at least one admission.
  3. Average, maximum and minimum seats per course type.
  4. Number of admissions per month of 2019.
  5. Courses with more than two admissions, most popular first.
  6. For each student, the number of courses they are admitted to.
  7. Students admitted to more than one course.
  8. Each society with its member count, including empty societies.
  9. The society with the most members (course Q10).
  10. The two most popular part-time courses (course Q11).
  11. Course types where the average seat count exceeds 45.
  12. For each teacher, the number of courses and total seats they are responsible for.
  13. Percentage of total college seats represented by each course.
  14. Courses that are more than 90 per cent full.

C. Predict and explain

  1. 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)
  2. 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)
  3. A report of courses and enrolments shows 4 courses instead of 5. Diagnose and give two fixes.

D. Analytical

  1. Explain why placing a non-aggregate condition in HAVING rather than WHERE gives the same answer but is slower. Illustrate with row counts.
  2. Write two queries answering "the average number of students per course", one counting empty courses and one not. Explain when each is appropriate.
  3. Explain what MySQL does with an invalid GROUP BY when ONLY_FULL_GROUP_BY is off, and why this is dangerous.
  4. 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.
  5. 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

  1. AVG(col) equals SUM(col)/COUNT(*).
  2. HAVING can be used without GROUP BY.
  3. COUNT(DISTINCT col) includes NULL as a distinct value.
  4. Every column in SELECT must appear in GROUP BY.
  5. WHERE can filter on COUNT(*).
  6. A LEFT JOIN with COUNT(*) correctly reports zero for unmatched rows.

Answers to Section E

  1. False. AVG divides by the number of non-NULL values; COUNT(*) counts all rows. They agree only when the column has no NULLs.
  2. True. Without GROUP BY, the whole table is one group, so HAVING COUNT(*) > 100 is a valid filter on that single group.
  3. False. It counts distinct non-NULL values only.
  4. False. It must appear in GROUP BY or be inside an aggregate function.
  5. False. WHERE is evaluated before groups exist; aggregate conditions belong in HAVING.
  6. False. COUNT(*) counts the NULL-padded row and reports 1. Use COUNT(<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 BY gives one row per group, so the single-value rule applies: every selected column must be grouped or aggregated.
  • WHERE filters rows before grouping and cannot see aggregates; HAVING filters groups after grouping and can. Put a condition in WHERE whenever it is testable on one row.
  • To count zeros, use LEFT JOIN and COUNT(<right-table column>) — both details are required.