Find duplicate rows
List the values that appear more than once, with HAVING COUNT(*) > 1.
SELECT customer_name, region, COUNT(*) AS copies
FROM customers
GROUP BY customer_name, region
HAVING COUNT(*) > 1;How it works
Group by the columns that should be unique together, then keep only the groups with more than one row. HAVING filters after grouping, which is why it is used here instead of WHERE.
To see the individual duplicate rows, join this result back to the table on the same columns.
What your tables need
- customers(customer_id, customer_name, region) - orders(order_id, customer_id, order_date, amount, status)
Rename the tables and columns to match your own database. Dates are stored as text in the form 2024-03-31 (or as a real date type, which works the same way here).
Small example
Input data
customers
customer_id | customer_name | region
1 | Ava | North
2 | Ben | South
3 | Cara | North
4 | Dan | East
5 | Ava | NorthExpected result
customer_name | region | copies
Ava | North | 2Common mistakes and limitations
- Differences in case or spaces ("Ava" and "ava ") hide duplicates. Compare LOWER(TRIM(name)) if that is what you mean. - NULL values: GROUP BY treats all NULLs as one group, but a comparison with = does not. - Deleting duplicates is a separate, riskier step. Keep one row per group by its smallest key, and back up first.