The group of one
A canteen report says: "Year 13 average rating for salad: 5." There is only one Year 13 student in the survey. Everyone now knows exactly what that student said.
Totals and averages hide people. But only if the groups are big enough.
Key words
| Word | What it means |
|---|---|
| Personal data | Information about a living person who can be identified. |
| Aggregation | Combining many rows into one summary, such as a count or an average. |
| Group | A set of rows that share a value, such as the same year group. |
| Small number | A group so small that a total could point to one person. |
| Threshold | The smallest group size you allow in a report. This lesson uses 3. |
| Primary use | Using data for the reason it was first collected. |
| Secondary use | Using data for a new reason after it was collected. |
| Database | An organised store of data in tables. |
| SQL | The language used to ask a database questions. |
| Query | A question you ask a database. |
The cells students fill in are left blank in this preview.
By the end of this lesson
Note
I can aggregate data with SQL and hide groups that are too small, so a report cannot point to one person.
Starter: last lesson
Have a go
Starter: last lesson
3 questionsYour answers are marked here and are not kept. A college account keeps every student’s marks.
Ways to aggregate
Aggregation turns many rows into a few numbers. Common methods are a count, a total, an average, a smallest and largest value, and a band such as "ages 15 to 16".
The report shows the summary. The single rows stay in the secure system.
Methods of aggregation in SQL
| Method | SQL word | Shows |
|---|---|---|
| Count | COUNT(*) | How many rows in each group |
| Total | SUM(column) | The rows added up |
| Average | AVG(column) | The usual value |
| Smallest and largest | MIN and MAX | The range |
| Group | GROUP BY | One summary line per group |
The cells students fill in are left blank in this preview.
Try it: count and average by year group
SELECT year_group, COUNT(*) AS students, AVG(rating) AS average_rating
FROM survey
GROUP BY year_group
ORDER BY year_group;Hide the small groups
Look at the Year 13 line. It has a count of 1. A small group can point to one person.
Pick a minimum group size, a threshold. The ICO does not give one number. Your policy sets it. Here we use 3. SQL hides small groups with HAVING.
Try it: only groups of 3 or more
SELECT year_group, COUNT(*) AS students, AVG(rating) AS average_rating
FROM survey
GROUP BY year_group
HAVING COUNT(*) >= 3
ORDER BY year_group;