CodeRang
Common problems

SQL find duplicates with GROUP BY and HAVING

SQL find duplicates queries start by defining which columns make two records equivalent. Learn how GROUP BY and HAVING expose repeated values, how normalization changes the comparison, and how duplicate rows can be removed while keeping one record.

Updated · SQL Arena team

SQL for QA testers trackAll problems

What you will practice

  • GROUP BY with HAVING COUNT(*) > 1
  • Value normalization before matching
  • ROW_NUMBER for duplicate selection
  • Composite duplicate keys
  • Data quality checks with SQL

A practice problem in the trainer

This is how a problem looks in SQL Arena: the task, the editor, the result and the database schema.

PrevNextRandom
TaskSubmissionsAI Mentor
Find duplicate emails after normalization

The customer_contacts table contains email addresses that may differ only by letter case or surrounding spaces. Find emails that occur more than once after normalization. Return the cleaned address as normalized_email and the number of matching records as duplicate_count.

Query 1PostgreSQLv16
SELECT LOWER(TRIM(email)) AS normalized_email,
       COUNT(*) AS duplicate_count
FROM customer_contacts
GROUP BY LOWER(TRIM(email))
HAVING COUNT(*) > 1
ORDER BY normalized_email;
Submit
Resultcustomerscustomer_contacts
Answer accepted

How to find duplicates in SQL

Grouping by the raw email column would miss some of the duplicates in this dataset. Alice@example.com is different text from a value containing lowercase letters and extra spaces, even though both represent the same address for this exercise. The Clara records have the same issue because their case differs.

LOWER(TRIM(email)) creates the comparison key before grouping. TRIM removes leading and trailing spaces, while LOWER standardizes case. The same expression appears in SELECT and GROUP BY, so the value shown in the output is exactly the value used to decide whether records belong to the same duplicate group.

HAVING COUNT(*) > 1 filters grouped results after aggregation. Only normalized emails with at least two source records remain. This is a detection query, not a deletion query. To remove duplicate rows safely, you still need a rule for which record should survive.

To find duplicates in SQL, first define the columns that make records equivalent. A duplicate may be based on one column or a combination of several columns. GROUP BY forms those groups, and HAVING COUNT(*) > 1 filters the result to groups that occur more than once. Removing duplicates requires an additional rule for deciding which row to keep. A common pattern uses ROW_NUMBER() over each duplicate group, orders rows by a stable criterion, keeps row number 1, and treats rows numbered 2 or higher as removable duplicates.

SQLPostgreSQL v16
SELECT LOWER(TRIM(email)) AS normalized_email,
       COUNT(*) AS duplicate_count
FROM customer_contacts
GROUP BY LOWER(TRIM(email))
HAVING COUNT(*) > 1
ORDER BY normalized_email;
Query result
normalized_emailduplicate_count
alice@example.com2
clara@example.com2
elena@example.com2

Common SQL duplicate detection and removal mistakes

01

Defining duplicates too literally

Exact string equality can miss records that are logically the same but formatted differently. Normalization may involve whitespace, case, phone formatting, punctuation, or other domain-specific rules. The correct transformation depends on the field.

02

Using DISTINCT as a duplicate cleanup strategy

DISTINCT only removes repeated rows from a query result. It does not tell you how many source records exist and does not clean the table. It is useful for presentation, but it is not a substitute for duplicate diagnosis.

03

Deleting duplicate groups without choosing a keeper

A duplicate condition identifies all members of a repeated group, including the record you probably want to retain. ROW_NUMBER() OVER (PARTITION BY ... ORDER BY ...) is useful because it lets you mark one row as the keeper and treat later rows separately.

Practice problems on this topic

SQL for QA testers track

Duplicate problems appear in more forms than repeated emails. Retry logic can create repeated operations, joins can fan one row out into several, text processing can repeat tokens, and event streams can contain near-duplicate actions within a short time window. SQL Arena includes free Easy problems on repeated email entries and join fan-out, followed by harder tasks where the duplicate rule depends on time or business keys. The “SQL for QA Engineers” track is a useful next step for this kind of data validation.

Reference on this topic

In-depth articles with syntax and examples in the SQL Arena reference.

Frequently asked questions

How do I find duplicates in SQL?

Group by the columns that define a duplicate and use HAVING COUNT(*) > 1. This returns groups that occur more than once.

How do I find duplicate rows in SQL using GROUP BY?

Put the duplicate-defining columns in GROUP BY and calculate COUNT(*). Add HAVING COUNT(*) > 1 to filter out values that appear only once.

How do I remove duplicate rows in SQL?

First define which duplicate row should be preserved. One common method assigns ROW_NUMBER() inside each duplicate group and removes rows with numbers greater than 1.

How do I find duplicates ignoring case and spaces in SQL?

Normalize the value before grouping, for example with LOWER and TRIM when that matches the data semantics. Group by the same normalized expression used for comparison.

What is the difference between DISTINCT and finding duplicates in SQL?

DISTINCT removes duplicate rows from a query result. GROUP BY with HAVING COUNT(*) > 1 identifies which values are duplicated and can also show how often they occur.

See also

Practice SQL find duplicates queries

SQL Arena includes problems on repeated values, join fan-out, repeated events, idempotency, and selecting one canonical record from a duplicate group. Answers are checked against hidden data rather than a fixed visible output. There are 1,192 tasks, separate PostgreSQL, MySQL, ClickHouse, and BigQuery sandboxes, and 292 free tasks available free without a card.

Practice duplicate SQL queries

More practice from CodeRang