Aggregation and Grouping
An aggregate function produces a single summary value from a set of tuples, and GROUP BY is what lets that summary be computed per bucket of rows rather than over the whole table. Together they turn a query from “which rows?” into “how many / how much / what is the extreme?”. Introduced in 2026-04-06-aggregation-grouping-and-multiple-relation-queries; practised in week7-tutorial-applied-class-6-aggregation-and-grouping and week7-tutorial-case-study-5-easydrive-insurance.
The aggregate functions
| Function | Returns |
|---|---|
COUNT |
The number of tuples |
SUM |
The sum of a set of numeric values |
AVG |
The average of a set of numeric values |
MAX / MIN |
The largest/smallest value of any domain with a total ordering — not just numbers |
MAX/MIN working on any ordered domain is the source of a recurring trap: dates order chronologically, so the youngest employee is the one with the largest (most recent) date of birth.
SELECT MAX(DOB) FROM Employee; -- youngest, not oldestAny aggregate can be applied with or without DISTINCT, which matters most for COUNT: COUNT(*) counts tuples, while COUNT(DISTINCT attr) counts how many different values of attr those tuples hold.
SELECT COUNT(*) FROM Employee WHERE dNum = 5;
SELECT COUNT(DISTINCT salary) FROM Employee WHERE dNum = 5;
-- How many distinct roles reach each website — not how many
-- Permission rows mention it
SELECT WebsiteURI, COUNT(DISTINCT RoleID)
FROM Permission
GROUP BY WebsiteURI;Aggregation functions may be used in the SELECT clause and the HAVING clause — and never in WHERE, which runs before any grouping exists (see sql-select-statement). To filter rows against an aggregate, compute it in a subquery: sql-nested-queries. To sort by one, name the target-list position the aggregate occupies rather than repeating the aggregate in ORDER BY.
Aggregating without GROUP BY
With no GROUP BY, the entire result set is treated as one group: the aggregate spans the whole table and the query returns a single row.
SELECT AVG(salary), SUM(salary) FROM Employee;
-- Several aggregates and an expression over them, in one row
SELECT MAX(Level), MIN(Level),
(MAX(Level) - MIN(Level)) AS DifferenceInAdministrativeEmployeeLevels
FROM AdministrativeEmployee;GROUP BY
GROUP BY and HAVING are stages 3 and 4 of the clause pipeline in sql-select-statement, which is where their ordering rules come from. WHERE filters rows first; GROUP BY then buckets the survivors, one group per distinct combination of the grouping attributes, and one output row per group.
-- Total employees per department
SELECT dNum, COUNT(*)
FROM Employee
GROUP BY dNum;
-- dNum | COUNT(*)
-- 1 | 1
-- 4 | 3
-- 5 | 4
-- Only employees earning > 40000 are counted
SELECT dNum, COUNT(*)
FROM Employee
WHERE salary > 40000
GROUP BY dNum;
-- Grouping by two attributes: one group per (department, gender) pair
SELECT dNum, sex, AVG(salary)
FROM Employee
GROUP BY dNum, sex;The rule: when GROUP BY is used, any attribute appearing in the SELECT clause must either also appear in the GROUP BY clause or be inside an aggregation function. There is no other option, because a group has exactly one value for each grouping attribute but many values for everything else — SQL would not know which one to print.
A useful degenerate case: grouping by a primary key or other unique field puts exactly one tuple in every group, so the query returns as many rows as the table has, and each aggregate just echoes that row’s own value.
HAVING — conditions on groups
HAVING filters groups the way WHERE filters rows, and unlike WHERE it can contain aggregates. The same attribute rule applies in its own words: any attribute appearing in the HAVING clause must either appear in the GROUP BY clause or be inside an aggregation function.
-- Departments with more than 2 employees
SELECT dNum, COUNT(*)
FROM Employee
GROUP BY dNum
HAVING COUNT(*) > 2;
-- Departments with more than 2 employees earning > 20000
SELECT dNum, COUNT(*)
FROM Employee
WHERE salary > 20000
GROUP BY dNum
HAVING COUNT(*) > 2;In the second query WHERE and HAVING both talk about the same filtered population (“employees earning over 20000”). Making HAVING test a different population — e.g. the department’s total headcount, not just its well-paid staff — needs a correlated subquery, see sql-nested-queries.
Idioms worth recognising:
-- "at least two X per group"
SELECT RoleId FROM Permission
WHERE GrantType = 'Edit'
GROUP BY RoleId
HAVING COUNT(WebsiteURI) >= 2;
-- "values shared by more than one row"
SELECT DISTINCT LastName FROM Employee
WHERE LastName IS NOT NULL
GROUP BY LastName
HAVING COUNT(FirstName) > 1;The IS NOT NULL guard there isn’t decoration. “Unknown” is not a shared surname, and the course never settles what a null does once it reaches a GROUP BY bucket or an aggregate — see sql-nulls for exactly what Module 3 does and doesn’t specify. Excluding nulls explicitly is the habit the material teaches, precisely so the answer doesn’t depend on a rule you were never given.
Comparing a group’s count against a count computed elsewhere is the basis of the counting form of relational-division — “this X is related to as many Ys as there are Ys, so it is related to all of them”.
Note that different HAVING conditions can coincide on a given data set without being equivalent: over singleton groups, COUNT(*) < 2, MAX(salary) <= MIN(salary) and SUM(salary) < 60000 may all select exactly the same groups, while AVG(salary) = 25000 picks out a different set. Reason about what each condition means per group, not about which rows it happens to return once.
Choosing the grouping attributes carefully
The GROUP BY list defines what “one thing” means for the whole query, so getting it wrong produces a plausible-looking but wrong count rather than an error. Counting customers per street by grouping Address on (Postcode, StreetName) overcounts if several customers share one AddressID — the question was about people per street, but the grouping was over address rows. Ask what a single output row is supposed to represent, and check that the grouping produces exactly one row per such thing; often the honest answer requires joining another table in first (sql-joins).