Duplicate detection
Support flags something odd: “I think we have the same customer twice. Alice from India emailed us twice this week and it read like two separate people.”
So I'm looking for two rows in customers that shouldn't both exist. Same email, presumably.
Right idea. How would you ask the database 'is there more than one row with this value'?
You’ve already got the tool for this — it’s GROUP BY. Group the rows by the value you suspect is duplicated, count how many rows land in each group, then keep only the groups where that count is more than one. That’s HAVING COUNT(*) > 1 — “which groups have HAVING more than 1 member,” the same clause from the aggregation chapter, aimed at a new problem.
Group by the suspect value. Keep groups bigger than one.
SELECT email, COUNT(*) AS n
FROM customers
GROUP BY email
HAVING COUNT(*) > 1;Running…
Zero rows. Not an error — the query ran fine, it just found nothing. That’s a real, useful answer: no email address at NOVA appears twice. Support’s hunch, checked properly, turned out wrong. Empty results aren’t failed queries — sometimes the honest answer is “no.”
Harry: “Okay, what about phone number, then? Maybe two people share a number by mistake.”
SELECT phone, COUNT(*) AS n
FROM customers
GROUP BY phone
HAVING COUNT(*) > 1;| phone | n |
|---|---|
| NULL | 3 |
That found something — but look closer. The “duplicated” value is NULL, shared by three rows. That’s Chloe, Farah, and Julia, and none of them share anything — they just never gave NOVA a phone number. GROUP BY quietly treats every NULL as belonging to the same group as every other NULL, even though back in the WHERE chapter you learned NULL = NULL isn’t even true. Grouping and comparing follow different rules here.
GROUP BY puts every NULL in one bucket together, even though NULL never equals NULL in a comparison.
Exclude the unknowns before you go looking for real duplicates:
SELECT phone, COUNT(*) AS n
FROM customers
WHERE phone IS NOT NULL
GROUP BY phone
HAVING COUNT(*) > 1;Running…
Empty again, correctly this time — every phone number NOVA actually has on file is genuinely unique. Two honest checks, two honest “no”s. The pattern didn’t fail either time; it did exactly its job, which was to find out.
Try it: What if you check name instead of email — could two different customers just happen to share a name?
Run your query to see results here.
Still empty. Twelve customers, twelve distinct names. The pattern is identical no matter which column you point it at — GROUP BY column HAVING COUNT(*) > 1 works the same on email, phone, name, or anything else you’re suspicious of.
Compliance has a different question: are any salary amounts at NOVA shared by more than one employee? List every such salary along with how many employees earn it, highest first.
Hint 1
Same pattern as the customers checks — you're just pointing it at a different table and column now.
Hint 2
GROUP BY salary, then keep only the groups where COUNT(*) is more than one.
Hint 3
SELECT salary, COUNT(*) AS n FROM employees GROUP BY salary HAVING COUNT(*) > 1 ORDER BY salary DESC;
Solution
Running…
This time the pattern actually finds something: $128,000, $121,000, and $105,000 are each held by exactly two employees. That’s not a data problem — it’s just how compensation bands work — but it’s the same query shape as the “is this a mistake” checks against customers. The pattern doesn’t know or care whether a match is a bug or a perfectly normal coincidence; it just tells you where the counts are higher than one. Deciding what that means is on you.
To find duplicates, GROUP BY the suspect column and keep groups with HAVING COUNT(*) > 1. Watch for NULL: GROUP BY quietly collapses every NULL into one group, so filter it out first with WHERE column IS NOT NULL, or “missing values” will masquerade as duplicates.