Higher Computing Science
SQL GROUP BY with aggregates and calculations
Higher Computing Science: Database design and development
A GROUP BY query, to write or to work out, was in the database section of every Higher paper from 2023 to 2026. The 2024 examiners' report wanted GROUP BY explained in terms of the query's own field, and the 2025 report noted that only some candidates could put a calculation inside an aggregate.
The data
A school's sponsored walk records each walker's house and laps, and each sponsor's pledge per lap.
| walkerID | forename | house | laps |
|---|---|---|---|
| W1 | Amy | Arran | 12 |
| W2 | Ben | Bute | 8 |
| W3 | Cara | Arran | 10 |
| W4 | Dev | Cairn | 15 |
| W5 | Ewan | Bute | 6 |
| pledgeID | walkerID | ratePerLap |
|---|---|---|
| P1 | W1 | 0.50 |
| P2 | W1 | 1.00 |
| P3 | W2 | 2.00 |
| P4 | W3 | 0.50 |
| P5 | W4 | 1.00 |
| P6 | W4 | 0.20 |
| P7 | W5 | 1.50 |
| P8 | W3 | 1.00 |
The query
How much did each house raise, highest first?
SELECT house, SUM(laps * ratePerLap) AS [Raised] FROM Walker, Pledge WHERE Walker.walkerID = Pledge.walkerID GROUP BY house ORDER BY SUM(laps * ratePerLap) DESC;
Working out the output
- Join and filter (WHERE): each pledge is matched to its walker, giving 8 rows.
- Group: the rows are put into one group per house.
- Calculate in each group: laps × ratePerLap for every row, added up. Arran: Amy 12 × 0.50 + 12 × 1.00 = 18, and Cara 10 × 0.50 + 10 × 1.00 = 15, so 33. Bute: 8 × 2.00 + 6 × 1.50 = 25. Cairn: 15 × 1.00 + 15 × 0.20 = 18.
- Sort: Arran 33, Bute 25, Cairn 18.
Without GROUP BY, the SUM would give one total for the whole walk, 76, not one per house.
- Explaining GROUP BY: "it makes SUM give one total for each house, instead of one total for every pledge". Use the query's field.
- Predicting output: usually a mark for the right groups and a mark for the right values.
- Writing it: the aggregate with its alias, every table with its join, GROUP BY the field shown, and the clauses in order: SELECT, FROM, WHERE, GROUP BY, ORDER BY.
- Calculations go inside the aggregate:
SUM(laps * ratePerLap)works out the value for every row, then adds them up.
Try two exam-style questions
Choose an answer, say how sure you are, then press Check. Every wrong answer tells you why it is wrong.
The full course covers every topic like this. Every topic in Higher Computing Science has a short note, and a practice quiz with feedback on every answer, plus coding lessons in Python, SQL and HTML, a full mock exam and a progress report for parents.
More guides
This site is independent: it is not connected to the SQA or Qualifications Scotland. Points from the examiners' course reports are given in our own words.