Paid in once, counted twice
A member pays in £40. The row is typed in twice by mistake. The branch total is now £40 too high.
Nobody sees it by eye in a list of thousands. One short question, asked of the data, finds it.
Key words
| Word | What it means |
|---|---|
| Duplicate | a row that repeats another row |
| Query | a question you ask of data, written in SQL |
| SQL | a language for asking questions of data kept in tables |
| Group | rows put together because they share the same values |
| Primary key | a column whose value is different in every row |
| Dataset | a collection of data about one subject |
The cells students fill in are left blank in this preview.
By the end of this lesson
Note
I can write a query that finds repeated rows, and say which row of a pair to keep.
Starter
Have a go
Starter
3 questionsYour answers are marked here and are not kept. A college account keeps every student’s marks.
This is practice
Important
The real task comes from the exam board. This lesson uses an invented organisation and its own made-up data. Your teacher tells you how the real tasks are set and what you may bring.
Two kinds of repeat
Look at rows 2 and 4 of the deposit table. They have different ids, but the member, date, amount and branch are all the same.
The primary key is different, so the table does not see a repeat. You must choose which columns make a row "the same". Here that is member, date, amount and branch.
Run it: group and count
SELECT member_ref, deposit_date, amount, branch, COUNT(*) AS times
FROM deposit
GROUP BY member_ref, deposit_date, amount, branch
HAVING COUNT(*) > 1;Worked example: what it printed
| member_ref | deposit_date | amount | branch | times |
|---|---|---|---|---|
| M002 | 2026-01-05 | 40.0 | North | 2 |
The cells students fill in are left blank in this preview.
Why HAVING and not WHERE
WHERE filters rows before they are grouped. HAVING filters the groups after they are counted.
You can only know a group has more than one row after you have counted it. So the count goes in HAVING.