A3.3.4 · Database programming

Calculations with aggregate functions

A bike shop owner does not want to read eight hundred repair records. She wants four numbers: how many, how much, the cheapest, the dearest. That is the entire job of an aggregate function.

HL only Command term: construct Builds on A3.3.2

Explainer

Every query so far returned rows. SELECT with a JOIN returns rows, WHERE returns fewer rows, ORDER BY returns the same rows rearranged. An aggregate function does something different in kind: it reads many rows and returns a single calculated value.

Every SELECT so far has handed back rows, one row per matching record. An aggregate function breaks that pattern: it reaches across as many records as match, and hands back a single number summarizing all of them. The five on the syllabus are COUNT, SUM, AVERAGE, MIN and MAX.

Four of the five need a numeric field, since you cannot total or average text. COUNT is the exception: it counts records, so it works on any field at all.

The syllabus says AVERAGE, the database says AVG. The assessment statement names the function AVERAGE, but almost every real system implements it as AVG(...), and that is the form real SQL implementations use. Write AVG in the query and use the word "average" when describing what it does. Exact syntax varies between database systems, which the syllabus states outright, so a marker is reading for the right function, not one specific spelling.

The five functions

Function	What it returns	Example
COUNT	How many matching rows there are. This is the only one of the five that makes sense on a text field, since it never looks at the values themselves, just tallies rows.	SELECT COUNT(RepairID) FROM Repair;
SUM	Every matching value in that column, added together into one running total.	SELECT SUM(PartsCost) FROM Repair;
AVG	The mean of every matching value. The syllabus calls this AVERAGE, but the function itself is written AVG.	SELECT AVG(PartsCost) FROM Repair;
MIN	Whichever matching value is lowest, found by scanning them all and keeping the smallest.	SELECT MIN(PartsCost) FROM Repair;
MAX	The mirror of MIN, whichever matching value comes out highest.	SELECT MAX(PartsCost) FROM Repair;

Explainer

How GROUP BY changes the answer

Used alone, an aggregate function gives one number for the whole table. That is often not the number anyone wants.

Without GROUP BY, the entire table is treated as one pile, and the function returns exactly one row: one total, one average, one maximum.

With GROUP BY, the rows are first sorted into piles by the value of some column, and the function then runs separately on each pile. Five bike types means five answers, not one.

This is where the reporting value comes from. "Total parts cost: 1000.00" tells the owner almost nothing. "Electric bikes: 570.00 across only 2 repairs" tells her what to stock and what to charge. Click through and watch the collapse happen.

Step 1 of 4 All the rows

Worked example

REPAIR: building the shop report one function at a time

The same eight repair records, answering a different business question at every step.

Step 1 of 6 COUNT

Try it yourself

Run your own queries

The same two tables, live. Type a statement and run it. Reading a worked example proves nothing until you have written one and watched it fail, so the errors here are written to tell you what is actually wrong rather than just refusing. Everything A3.3 teaches works, not only this statement's own commands, so the INSERT, UPDATE and DELETE from A3.3.3 run here too and really do change the tables until you reset them.

RepairRepairID, BikeType, MechanicID, PartsCost, LabourHours
8 rows

MechanicMechanicID, MechanicName, Station
3 rows

SQL query

Ctrl + Enter also runs

Things worth trying. Put an aggregate inside WHERE and see what it says. Remove GROUP BY from a query that selects a plain column next to a SUM. Ask for AVERAGE(PartsCost) using the syllabus spelling instead of AVG. Each of those is a real exam error, and it is much easier to remember one you have triggered yourself.

Check your understanding

Why does SELECT BikeType, MechanicID, SUM(PartsCost) FROM Repair GROUP BY BikeType; fail?

Common mistake

Markers reported this pattern for A3.3.4

RecurringConstructed queries frequently select a column that is neither wrapped in an aggregate function nor listed in the GROUP BY. The query looks reasonable and reads well in English, but it asks the database for a value that does not exist: one name, one date or one ID for a group that contains many different ones.

What a weak answer looks like: SELECT BikeType, MechanicID, SUM(PartsCost) FROM Repair GROUP BY BikeType; The Mountain row would have to show one MechanicID, but three different mechanics worked on mountain bikes. There is no correct value to print.

✓

What it should say: either drop the loose column, SELECT BikeType, SUM(PartsCost) ... GROUP BY BikeType;, or group by it as well, GROUP BY BikeType, MechanicID;, which gives one row per bike type per mechanic. Check every column in the SELECT list against that rule before writing the query down.

Revision questions

Past-paper style. Write an answer, then reveal the marking points and mark yourself.

Question 1 · 2 marks

[2]Construct a query that returns the total parts cost of every repair in the REPAIR table.

Question 2 · 4 marks

[4]Construct a query that shows, for each bike type, the number of repairs and the average parts cost.

Question 3 · 5 marks

[5]Construct a query joining REPAIR and MECHANIC that lists each mechanic's name and their highest single parts cost, showing only mechanics who have completed more than two repairs.

Flashcards

Active recall for this statement's key terms.