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.

Practise this in the app: instant marking and a fix for every mistake. Free, no email needed to start.

Practise this lesson

Aggregate functions

Without GROUP BY they summarise the whole result:

SELECT COUNT(*) AS Hires, SUM(Days) AS TotalDays, ROUND(AVG(Days), 2) AS AverageDays FROM tblHire

Result (1 row):

Hires | TotalDays | AverageDays
------+-----------+------------
8     | 17        | 2.13

COUNT(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 BikeType

Result (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.00

In 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

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

Result (2 rows):

FirstName | Surname | Hires | TotalDays
----------+---------+-------+----------
Amira     | Hassan  | 3     | 6
Chen      | Li      | 2     | 2

In 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 DESC

Result (4 rows):

BikeType | Income
---------+-------
Electric | 175.00
Mountain | 152.00
Road     | 120.00
Hybrid   | 57.50

Worked example

Find the number of hires and total days hired in each month, using the first 7 characters of HireDate as the month.

  1. ```sql SELECT substr(HireDate, 1, 7) AS Month, COUNT(*) AS Hires, SUM(Days) AS TotalDays FROM tblHire GROUP BY Month ORDER BY Month ```
  2. Result (3 rows): ``` Month | Hires | TotalDays --------+-------+---------- 2026-03 | 4 | 7 2026-04 | 3 | 7 2026-05 | 1 | 3 ```
  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.

Key idea: COUNT, SUM, AVG, MIN, MAX; GROUP BY gives one row per group (non-aggregated fields must be grouped); WHERE filters rows before grouping, HAVING filters groups after; name every calculated column.

Check yourself

  1. 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.

  2. 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.

  3. 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.

  4. 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.

  5. 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.

  6. 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.

  7. 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.

  8. 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.

Practise with instant feedback

Sources

Last updated 8 November 2026. Spotted a mistake? Tell us.