JuniorCodeCommonNot answered yet
Write a query to find duplicate emails
Return every email that appears more than once in the table.
-- Person(id, email)
-- write your query hereWrite the query.
Group by the column and keep groups of size > 1: SELECT email FROM Person GROUP BY email HAVING COUNT(*) > 1. You must filter the aggregate with HAVING, not WHERE — WHERE is evaluated before rows are grouped, so it cannot see COUNT(*).
- ✗Putting
COUNT(*)inWHEREinstead ofHAVING - ✗Thinking
DISTINCTfinds duplicates rather than removing them - ✗Expecting a naive self-join to isolate only the duplicates
- →Why can
WHEREnot filter on an aggregate likeCOUNT(*)? - →How would you also return how many times each duplicate email appears?
Contents
Task
Return every email in Person that appears more than once.
Solution
SELECT email
FROM Person
GROUP BY email
HAVING COUNT(*) > 1;Key points
GROUP BYcollects rows per email;COUNT(*)measures each group's size.HAVINGfilters the already-aggregated groups;WHEREwould run before grouping.- To see the repeat count, add
COUNT(*)to the select list.
Contents
