Finding duplicate values in a SQL table

Question

It's easy to find duplicates with one field:

SELECT email, COUNT(email) 
FROM users
GROUP BY email
HAVING COUNT(email) > 1

So if we have a table

ID   NAME   EMAIL
1    John   asd@asd.com
2    Sam    asd@asd.com
3    Tom    asd@asd.com
4    Bob    bob@asd.com
5    Tom    asd@asd.com

Because they all have the same email, this query will return John, Sam, Tom, and Tom. To receive duplicates with the same email and name is what I actually want, though.

I want to obtain "Tom," "Tom," in other words.

I made a mistake and permitted the insertion of duplicate name and email values, which is why I require this. I must first locate the duplicates before I can remove or modify them.

Can someone please help me with this?

Answer 1

SELECT
    name, email, COUNT(*)
FROM
    users
GROUP BY
    name, email
HAVING 
    COUNT(*) > 1

Just group on the two columns.

Note: The concept of  "functional dependency": has replaced the former ANSI standard, which called for grouping by all non-aggregated columns:

In relational database theory, a functional dependency is a constraint between two sets of attributes in a relation from a database. In other words, functional dependency is a constraint that describes the relationship between attributes in a relation.

Support is not consistent:

• Recent PostgreSQL supports it.
• SQL Server (as at SQL Server 2017) still requires all non-aggregated columns in the GROUP BY.
• MySQL is unpredictable and you need sql_mode=only_full_group_by:
  • GROUP BY lname ORDER BY showing wrong results;
  • Which is the least expensive aggregate function in the absence of ANY() (see comments in accepted answer).
• Oracle isn't mainstream enough (warning: humour, I don't know about Oracle).

 I hope this helps you.