A3.3.4 · Database programming
Aggregate functions
By the end you can
Construct calculations with COUNT, SUM, AVG, MIN, MAX, GROUP BY and HAVING.
Businesses want answers, not lists: how many orders, what was the total, which was the biggest. calculate a single value from many rows.
The five functions#
COUNT(*)counts rows.COUNT(col)counts rows where the column is not empty, andCOUNT(DISTINCT col)counts the different values.SUM(col)adds a numeric column.AVG(col)gives the mean.MIN(col)andMAX(col)give the smallest and largest value.
Add a WHERE and they work on just the rows that match: SELECT SUM(Price) FROM Book WHERE Genre = 'Technology'. You can calculate inside them too: SUM(Price * Qty) is revenue.
GROUP BY and HAVING#
With GROUP BY the database sorts the rows into groups with the same value and gives one result row per group. It is how you ask “how many books in each genre?” or “how much has each customer spent?”.
HAVING then filters the groups. The order of events is: WHERE removes rows, GROUP BY builds the groups, the aggregates are worked out for each group, and HAVING removes groups. That is why an aggregate can appear in HAVING but never in WHERE.
One rule catches many students: every column in the SELECT list must either be inside an aggregate function or be listed in GROUP BY. A result row stands for a whole group, so it can only show something that is the same throughout the group. (Some textbooks show queries that break this rule; this sandbox rejects them on purpose, and so do most databases.)
Author
| AuthorIDPK | Name | Country |
|---|---|---|
| 1 | Maya Okafor | Nigeria |
| 2 | Liam Chen | China |
| 3 | Sofia Rossi | Italy |
| 4 | Arun Patel | India |
| 5 | Noor Haddad | Egypt |
5 rows. AuthorID INT, Name TEXT(40), Country TEXT(20).
How many books are in the shop?
The number of orders placed.
The total sales value.
The mean price of a book.
The most expensive book.
One result row for each genre.
Only the genres with more than two books.
0 of 6 matched
Check yourself
Aggregates and grouping
Quick questions, made fresh every time and marked here. Do a few until they feel easy.
Exam practice
Paper 1-style questions
5 questions · 15 marks
Write your answer as you would in the exam, then show the mark points and tick each one your answer made. These are our own questions in the style of the exam, not past-paper questions.
1.State the SQL aggregate functions that (a) count the rows, (b) add up a column, (c) find the largest value.
[3]State: Give a short answer, no explanation needed.
2.Table Purchase(PurchaseID, ItemID, Quantity, CustomerID) records sales. Construct a statement that shows the total quantity sold to the customer with CustomerID 3.
[3]Construct: Draw or write it out (a table, a diagram, a program).
3.Table Customer(CustomerID, CustomerName, CustomerCountry). Construct a statement that shows each country with the number of customers in it, only for countries with more than 2 customers.
[4]Construct: Draw or write it out (a table, a diagram, a program).
4.Distinguish between the WHERE and HAVING clauses.
[3]Distinguish: Make the differences between two things clear.
5.Explain the difference between COUNT(ItemID) and COUNT(DISTINCT ItemID) on the Purchase table.
[2]Explain: Give reasons: say what happens AND why, or how one thing causes another.
In a nutshell
- Aggregate functions calculate one value from many rows: COUNT (how many), SUM (total), AVG (mean), MIN and MAX (smallest and largest).
- COUNT(*) counts rows; COUNT(column) counts non-empty values; COUNT(DISTINCT column) counts different values.
- GROUP BY splits the rows into groups and gives one result row per group: SELECT Genre, COUNT(*) FROM Book GROUP BY Genre.
- WHERE filters rows before grouping; HAVING filters groups after. Aggregates are not allowed in WHERE.
- A column in the SELECT list that is not inside an aggregate must also be in GROUP BY.