Database Design & Development · Implementation

DDD8 — SQL: aggregate functions, GROUP BY, computed values, aliases

⏱ ~100–110 minutes · double period

💻 DataGrip · standard SQL

Learning intentions

I can use the five aggregate functions: MIN, MAX, AVG, SUM, COUNT.
I can use GROUP BY to calculate an aggregate separately per category.
I can create computed values and give fields readable aliases.

Success criteria

I can write a query using an aggregate function with a correctly written alias.
I can decide when a query needs GROUP BY and write it in the correct clause order.
I can solve a "compare against an aggregate" problem using the two-separate-queries pattern.

Warm up — what do you already know?

Answer before the lesson begins — it's fine if you're unsure.

WU1

1. In DDD7, which SQL clause sorts the final results? Write the two SQL keywords in uppercase with one space between them.

WU2

2. In DDD6's Example 3 query design, why was grouping needed?

Because the count of classes had to be calculated separately for each instructor
Correct — "for each instructor" is the signal for grouping.
Because the query needed a sort order
Sort order and grouping are separate rows in the template.
Because the query used more than one table
Using more than one table doesn't by itself require grouping.
Because search criteria were needed
That query had no search criteria stated.

WU3

3. Which wildcard symbol matches any number of characters, used with LIKE? Write the symbol only.

Key vocabulary

Aggregate function

A function that calculates a single summary value from a set of records: MIN, MAX, AVG, SUM, COUNT.

GROUP BY

The clause that splits records into categories so an aggregate function calculates separately per category.

Computed value

A new value produced by an aggregate function or an arithmetic expression, appearing as its own column in the results.

Alias

A readable, custom name given to a field or computed value using AS, so results are clearly labelled.

Two-query pattern

Solving a "compare against an aggregate" problem using two separate, named queries, since single-statement subqueries are out of scope.

Summarising data with SQL

The five aggregate functions

An aggregate function calculates a single summary value from a whole set of records: MIN (smallest), MAX (largest), AVG (average), SUM (total), and COUNT (number of records). These five are the only aggregate functions in the Higher specification. ROUND, covered later in this lesson, is a genuinely useful related function — but it isn't itself one of the five aggregate functions, and shouldn't be listed as a sixth if you're ever asked to name them.

Mixing aggregate and non-aggregate fields — the GROUP BY rule

A query cannot freely mix an aggregate field (like COUNT(classCode)) with a non-aggregate field (like firstName) in the same SELECT unless GROUP BY is used. Without grouping, SQL has no way to decide which single firstName value should sit alongside a count calculated across many rows — GROUP BY resolves this by collecting rows into one group per distinct value of the grouped field, so the aggregate calculates once per group instead of once overall.

Computed values

A computed value is any result produced by processing existing data, rather than a value stored directly — this includes aggregate functions, but also simple arithmetic on ordinary fields, like calculating a total cost from feePerSession * numberOfSessions. A computed value becomes its own column in the results table, exactly as if it had been stored, even though it wasn't.

UPDATE preview — applying a computed value

Preview only — do not run this yet. A computed value can also be used to change stored data. This DDD9-style statement calculates a 5% increase from the existing fee, then stores the result only for Yoga:

UPDATE Class
SET feePerSession = feePerSession * 1.05
WHERE classCode = 'YOG01';

Aliases: AS ReadableName

Without an alias, a computed value's column heading is the expression itself (e.g. AVG(feePerSession)), which is technically correct but not reader-friendly. AS ReadableName gives it a proper label. Use a portable CamelCase identifier with no spaces, such as AS AverageFee.

Clause order: GROUP BY before ORDER BY

SQL's clause order is fixed: SELECT, FROM, WHERE, GROUP BY, then ORDER BY last. Reversing GROUP BY and ORDER BY is a genuine syntax error, not just a style issue — a useful memory aid is GO: Group by, then Order by, always in that order.

Comparing against an aggregate: the two-query pattern

A single SQL statement cannot nest one query's aggregate result directly inside another query's WHERE clause at Higher level — subqueries are explicitly outside the specification. Instead, "compare against an aggregate" problems (like "which class has the highest fee?") are solved as two separate, named queries: Query 1 calculates the single aggregate value; Query 2's WHERE clause then uses that value directly, once you know what it is.

Recognition only — SQA alias style

SQA past papers sometimes write aliases in square brackets — AS [Number Of Bookings]. Recognise that style when reading exam SQL, but always write the portable form (AS NumberOfBookings) — square brackets are a syntax error in MySQL/DataGrip.

Worked examples

Example 1 — A simple aggregate with alias and ROUND

Requirement: "What is the average fee per session across all classes, rounded to 2 decimal places?"

One overall value, no grouping needed: AVG(feePerSession), wrapped in ROUND(..., 2).

✓

Give it a readable alias. See the statement below.

SELECT ROUND(AVG(feePerSession), 2) AS AverageFee
FROM Class;

Example 2 — DDD6's per-instructor count, in SQL

Non-aggregate fields (firstName, surname) mixed with an aggregate (COUNT(classCode)) — GROUP BY is required.

Join Instructor and Class first, then group by instructorRef — every instructor's classes are counted separately.

✓

Sort by the alias, most classes first — GROUP BY comes before ORDER BY. See the statement below.

SELECT Instructor.firstName, Instructor.surname, COUNT(Class.classCode) AS NumberOfClasses
FROM Instructor, Class
WHERE Instructor.instructorRef = Class.instructorRef
GROUP BY Instructor.instructorRef
ORDER BY NumberOfClasses DESC;

GROUP BY instructorRef collapses the seven joined rows into one row per instructor — COUNT(classCode) returns INS01 = 3, INS02 = 2, and INS03 = 2.

Example 3 — The two-query pattern: which class has the highest fee?

Query 1 finds the single highest fee value across all classes.

Run Query 1 and read off its single result: 12.50.

✓

Query 2 uses that value directly in its own WHERE clause to find the matching class(es).

-- Query 1
SELECT MAX(feePerSession) AS HighestFee
FROM Class;

-- Query 2 (using Query 1's result, e.g. 12.50)
SELECT className, feePerSession
FROM Class
WHERE feePerSession = 12.50;

Now you try

Write a SQL statement to find the total number of bookings ever made, with the alias TotalBookings.

SELECT COUNT(bookingRef) AS TotalBookings
FROM Booking;

Practical activity — in DataGrip

💻 Now do this for real

Using the same craigmillar-sports-club-setup.sql database from DDD7, write and run these queries yourself rather than only checking them against a model answer.

Write a query giving the total combined capacity of every class, using AS TotalCapacity.
Expected: 109
Write a query showing how many classes run on each day of the week, using AS NumberOfClasses, sorted with the busiest day first.
Expected: Monday 2, Wednesday 2, then Tuesday/Thursday/Friday 1 each
Use the two-query pattern to find which class has the lowest fee. Run Query 1 first and read off the value before writing Query 2.
Expected Query 1 result: 5.50; Query 2 result: Zumba

If Query 2 in task 3 returns zero rows, check you used the exact value Query 1 returned — a common slip is retyping it with the wrong number of decimal places.

⚠️ Common mistakes — examiner feedback

Mixing aggregate and non-aggregate fields without GROUP BY. This causes an error or a meaningless single-row result — any non-aggregate field alongside an aggregate function needs GROUP BY.
Writing ORDER BY before GROUP BY. The fixed clause order is SELECT, FROM, WHERE, GROUP BY, ORDER BY — remember with GO.
Listing ROUND as a sixth aggregate function. Only MIN, MAX, AVG, SUM, and COUNT are aggregate functions — ROUND is a scalar numeric function, even though it is often used alongside them.
Trying to nest a query inside a WHERE clause directly. Subqueries are out of scope at Higher — use the two-separate-queries pattern instead.

📝 Exam tip

When a question describes a "compare against an aggregate" problem, write it explicitly as two labelled queries (Query 1, Query 2) rather than attempting a single nested statement — this is SQA's own examinable method, and a single-statement subquery answer may not gain full marks even if it technically works in some SQL products.

Task Set B — Core questions

Work through all questions. Written and code answers use self-assessment — compare with the model answer.

Name the five aggregate functions in this order: uppercase, separated by a comma and one space.

Which clause must be used whenever a query mixes an aggregate field with a non-aggregate field?

ORDER BY
ORDER BY sorts results — it doesn't resolve the aggregate/non-aggregate mixing problem.
GROUP BY
Correct — GROUP BY is required whenever aggregate and non-aggregate fields are combined.
LIKE
LIKE is for wildcard pattern matching, unrelated to this rule.
WHERE
WHERE filters records before aggregation, but doesn't resolve the mixing rule.

Write a SQL statement to find the total combined fee-per-session value across all classes, aliased as TotalFees.

SELECT SUM(feePerSession) AS TotalFees
FROM Class;

Write a SQL statement to find the earliest (minimum) dateJoined value in Member, aliased as EarliestJoinDate.

SELECT MIN(dateJoined) AS EarliestJoinDate
FROM Member;

Read and explain this SQL statement:

SELECT Class.className, COUNT(Booking.bookingRef) AS NumberOfBookings FROM Class, Booking WHERE Class.classCode = Booking.classCode GROUP BY Class.classCode;

Model answer

Write the two-query pattern to find which class has the highest fee. Query 1 must return one scalar value. Run it first, read its result of 12.50, then type that literal into Query 2's WHERE clause.

-- Query 1: run this first; it returns one value, 12.50
SELECT MAX(feePerSession)
FROM Class;

-- Query 2: type Query 1's result into WHERE as a literal
SELECT className, feePerSession
FROM Class
WHERE feePerSession = 12.50;

Why is a single nested subquery not the expected method for "compare against an aggregate" problems at Higher?

Because subqueries are explicitly stated as beyond the scope of the Higher course
Correct — the SQA spec states this directly; the two-separate-queries pattern is the examinable method instead.
Because subqueries don't work in DataGrip
DataGrip does support subqueries technically — the reason is about exam scope, not tool capability.
Because aggregate functions can't be used inside WHERE at all
This isn't accurate in general SQL — the actual reason is that Higher's specification excludes subqueries specifically.
Because GROUP BY replaces the need for subqueries entirely
GROUP BY solves a different problem (per-category aggregation) — it doesn't replace the two-query comparison pattern.

What does the "O" stand for in the GO mnemonic for clause order? Write the two SQL keywords in uppercase with one space between them.

Explain what a computed value is, and give one example that is an aggregate function and one example that is not.

Model answer

Task Set C — Extension · Beyond the specification

Optional challenge questions. Not required for your exam.

Explain why ROUND is genuinely useful alongside aggregate functions, but is still correctly excluded from the list of five aggregate functions.

One possible answer

In Example 2, the query grouped by Instructor.instructorRef rather than Instructor.surname. Would grouping by surname instead have worked correctly? Explain the risk.

One possible answer

What is a genuine practical limitation of the two-query pattern, compared to a real single-statement subquery (even though subqueries aren't examinable at Higher)?

One possible answer

📁 File this in OneNote under:
Higher Computing Science → Database Design & Development → DDD8

📌 Teacher notes — Shift+T to hide

Suggested timing: 8 min warm up · 15 min notes (aggregates, GROUP BY, computed values including the short UPDATE preview, aliases, GO mnemonic) · 15 min worked examples, ideally live in DataGrip · 15 min practical activity (real queries, real results) · 20 min Task Set B · 5–10 min Task Set C / review, if time allows.

Key misconception: pupils very commonly try to write ORDER BY before GROUP BY, or forget GROUP BY entirely when mixing aggregate and non-aggregate fields. The GO mnemonic (from Chris Meechan's materials) is worth repeating verbally every time a GROUP BY query is demonstrated.

Spec-accuracy note: do not let ROUND slip into an auto-checked "name the aggregate functions" answer — B1's expected answer is exactly MIN, MAX, AVG, SUM, COUNT, per the confirmed spec reading in ddd/DDD.md §1 and §9 item 3.

Correction (13 Jul 2026, completed 19 Jul 2026): B6 first used a nested subquery, then an attempted repair introduced an off-register grouping filter; neither matched the Higher two-query method. B6 now follows Example 3 honestly: Query 1 is the single scalar MAX(feePerSession), its result 12.50 is read by the pupil, and that literal is typed into Query 2's WHERE. Replace either older version in printed or exported copies.

SQA command words covered: "describe", "exemplify", "use" (write working aggregate SQL), and "read and explain code" (B5).

DDD8 — SQL: Aggregate Functions, GROUP BY, Computed Values, Aliases

DDD8 — SQL: aggregate functions, GROUP BY, computed values, aliases

Key vocabulary

Summarising data with SQL

The five aggregate functions

Mixing aggregate and non-aggregate fields — the GROUP BY rule

Computed values

UPDATE preview — applying a computed value

Aliases: AS ReadableName

Clause order: GROUP BY before ORDER BY

Comparing against an aggregate: the two-query pattern

Worked examples

Practical activity — in DataGrip