Finding duplicate values in a SQL table

0 votes

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?

Sep 16, 2022 in Database by Kithuzzz
• 38,010 points
669 views

1 answer to this question.

0 votes
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:

 I hope this helps you. 

answered Sep 17, 2022 by narikkadan
• 63,420 points

Related Questions In Database

0 votes
2 answers

How to select the nth row in a SQL database table?

SELECT * FROM ( SELECT ID, NAME, ROW_NUMBER() ...READ MORE

answered Apr 23, 2020 in Database by anand
• 140 points
25,045 views
0 votes
1 answer

MySQL query finding values in a comma separated string

To find MySQL query values in a comma-separated ...READ MORE

answered Oct 4, 2019 in Database by Daric
• 500 points
2,689 views
0 votes
1 answer

Ordering by the order of values in a SQL IN() clause

Use MySQL's FIELD() function: SELECT name, description, ... FROM ... WHERE id ...READ MORE

answered Feb 4, 2022 in Database by Neha
• 9,060 points
4,449 views
0 votes
1 answer

How to rename a column in a database table using SQL?

For SQL Server, use sp_rename USE AdventureWorks; GO EXEC sp_rename 'Customers.CustomerTerritory.TerritoryID', ...READ MORE

answered Feb 9, 2022 in Database by Neha
• 9,060 points
522 views
0 votes
1 answer

How to duplicate IDs with Hive QL / Impala / Python

Based on your example, supposing id2 always ...READ MORE

answered Oct 1, 2018 in Python by Priyaj
• 58,090 points
743 views
0 votes
1 answer

How to delete duplicate rows in SQL Server?

CTEs and ROW_NUMBER can be combined together which will ...READ MORE

answered Feb 10, 2022 in Database by Vaani
• 7,020 points
587 views
0 votes
1 answer

How to delete duplicate rows in SQL Server?

To answer your query, note that CTEs ...READ MORE

answered Feb 10, 2022 in Others by Soham
• 9,700 points
897 views
0 votes
1 answer

Finding duplicate rows in SQL Server

Use this : select o.orgName, oc.dupeCount, o.id from organizations ...READ MORE

answered Aug 14, 2022 in Data Science by narikkadan
• 63,420 points
336 views
0 votes
1 answer

Add a column with a default value to an existing table in SQL Server

Syntax: ALTER TABLE {TABLENAME} ADD {COLUMNNAME} {TYPE} {NULL|NOT ...READ MORE

answered Sep 12, 2022 in Database by narikkadan
• 63,420 points
2,042 views
0 votes
1 answer

Calculate a Running Total in SQL Server

The problem is that the SQL Server ...READ MORE

answered Sep 11, 2022 in Database by narikkadan
• 63,420 points
1,142 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP