All subjects · Information Technology · WIT14 3 hour practical exam on database solutions
Summary queries: aggregate functions, GROUP BY and HAVING
Edexcel IAL Information Technology · WIT14 3 hour practical exam on database solutions
Summaries answer the questions managers ask: how many, how much in total, what is the highest or average. Exam papers ask for Count, Sum, Max, Min and average in queries and reports, often grouped.
Aggregate functions
- COUNT(*) counts rows; COUNT(field) counts non-empty values; COUNT(DISTINCT field) counts different values.
- SUM(field) adds up; AVG(field) gives the mean; MIN and MAX give the smallest and largest.
Without GROUP BY they summarise the whole result:
SELECT COUNT(*) AS Hires, SUM(Days) AS TotalDays, ROUND(AVG(Days), 2) AS AverageDays FROM tblHireResult (1 row):
Hires | TotalDays | AverageDays
------+-----------+------------
8 | 17 | 2.13COUNT(Email) counts only customers with an email: 4 of 6.
GROUP BY
GROUP BY gives one summary row per group. Every non-aggregated field in SELECT must be in GROUP BY:
SELECT BikeType, COUNT(*) AS Bikes, MIN(DailyRate) AS Cheapest, MAX(DailyRate) AS Dearest
FROM tblBike
GROUP BY BikeType
ORDER BY BikeTypeResult (4 rows):
BikeType | Bikes | Cheapest | Dearest
---------+-------+----------+--------
Electric | 1 | 35.00 | 35.00
Hybrid | 2 | 10.00 | 12.50
Mountain | 2 | 18.00 | 22.00
Road | 1 | 20.00 | 20.00In Access, click Totals: the Total row shows Group By under BikeType and Count, Min, Max under the other fields. Rename calculated columns (Cheapest: DailyRate) rather than leaving names such as MinOfDailyRate or Expr1: the 2025 examiner report specifically advises this.
HAVING and WHERE
- WHERE filters rows before grouping.
- HAVING filters groups after aggregating, so it can test COUNT or SUM.
SELECT c.FirstName, c.Surname, COUNT(h.HireID) AS Hires, SUM(h.Days) AS TotalDays
FROM tblCustomer c
JOIN tblHire h ON h.CustomerID = c.CustomerID
GROUP BY c.CustomerID, c.FirstName, c.Surname
HAVING COUNT(h.HireID) >= 2
ORDER BY Hires DESC, c.SurnameResult (2 rows):
FirstName | Surname | Hires | TotalDays
----------+---------+-------+----------
Amira | Hassan | 3 | 6
Chen | Li | 2 | 2In the Access Total row, criteria under a Group By or aggregate column act like HAVING; choose Where in the Total row to filter rows before grouping.
Grouping across a join
Combine a join with GROUP BY to summarise related data, here total income by bike type:
SELECT b.BikeType, SUM(b.DailyRate * h.Days) AS Income
FROM tblHire h
JOIN tblHireItem i ON i.HireID = h.HireID
JOIN tblBike b ON b.BikeID = i.BikeID
GROUP BY b.BikeType
ORDER BY Income DESCResult (4 rows):
BikeType | Income
---------+-------
Electric | 175.00
Mountain | 152.00
Road | 120.00
Hybrid | 57.50Worked example
Find the number of hires and total days hired in each month, using the first 7 characters of HireDate as the month.
- ```sql SELECT substr(HireDate, 1, 7) AS Month, COUNT(*) AS Hires, SUM(Days) AS TotalDays FROM tblHire GROUP BY Month ORDER BY Month ```
- Result (3 rows): ``` Month | Hires | TotalDays --------+-------+---------- 2026-03 | 4 | 7 2026-04 | 3 | 7 2026-05 | 1 | 3 ```
- In Access you would group by a calculated field such as Month: Format([HireDate],"yyyy-mm").
Answer: One row per month with its count and total days.
Check yourself
- What single value does this query return on the Harbour Bike Hire database?
```sql
SELECT COUNT(*) FROM tblHire WHERE Days >= 2
```
Show answer
5. Hires of 2 or more days: 5.
- What single value does this query return on the Harbour Bike Hire database?
```sql
SELECT SUM(Days) FROM tblHire WHERE CustomerID = 1
```
Show answer
6. Amira's hires last 2, 1 and 3 days: 6 in total.
- What single value does this query return on the Harbour Bike Hire database?
```sql
SELECT AVG(DailyRate) FROM tblBike
```
Show answer
19.58. The mean daily rate is 19.58.
- How many rows does this query return on the Harbour Bike Hire database?
```sql
SELECT BikeType, COUNT(*) AS Bikes, MIN(DailyRate) AS Cheapest, MAX(DailyRate) AS Dearest
FROM tblBike
GROUP BY BikeType
ORDER BY BikeType
```
Show answer
4. There is one row for each different bike type: 4.
- How many rows does this query return on the Harbour Bike Hire database?
```sql
SELECT c.FirstName, c.Surname, COUNT(h.HireID) AS Hires, SUM(h.Days) AS TotalDays
FROM tblCustomer c
JOIN tblHire h ON h.CustomerID = c.CustomerID
GROUP BY c.CustomerID, c.FirstName, c.Surname
HAVING COUNT(h.HireID) >= 2
ORDER BY Hires DESC, c.Surname
```
Show answer
2. Customers with at least two hires: Amira, Hassan, 3, 6; Chen, Li, 2, 2. That is 2 rows.
- Which row comes first when this query is run?
```sql
SELECT b.BikeType, SUM(b.DailyRate * h.Days) AS Income
FROM tblHire h
JOIN tblHireItem i ON i.HireID = h.HireID
JOIN tblBike b ON b.BikeID = i.BikeID
GROUP BY b.BikeType
ORDER BY Income DESC
LIMIT 1
```
- Mountain, 116.00
- Road, 80.00
- Hybrid, 37.50
- Electric, 175.00
Show answer
Electric, 175.00. Electric bikes earn the most: Electric, 175.00.
- Which clause keeps only bike types with more than one bike?
- WHERE COUNT(*) > 1
- HAVING COUNT(*) > 1
- ORDER BY COUNT(*) > 1
- GROUP BY COUNT(*) > 1
Show answer
HAVING COUNT(*) > 1. Conditions on aggregates go in HAVING; WHERE runs before groups exist.
- What single value does this query return on the Harbour Bike Hire database?
```sql
SELECT COUNT(DISTINCT CustomerID) FROM tblHire
```
Show answer
5. Eight hires but only 5 different customers.
Sources
- Pearson Edexcel International Advanced Level in Information Technology specification (Issue 2, February 2021): Unit 4, Topics 17 to 19
- Pearson: WIT14/01 examiner report, June 2024
- Pearson: WIT14/01 examiner report, June 2025
Last updated 8 November 2026. Spotted a mistake? Tell us.