Finding Duplicates with SQL?

Question

I want to know if my database contains duplicates. If not, I know that my code is working.

My database looks like this:

---------------------------- | id | name | price | link | |---------------------------| | 202| test | 34.00 | googl | | 203| halo | 22.00 | bing | | 204| hovo | 31.00 | link | | 205| test | 34.00 | googl | -----------------------------

You can see that the values of name, price and link are they same but the id is different. I thought about something like group the name, price and link and count it with having. Like this for example:

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

But my problem is how can I group by 3 attributes like in my table. By name, price and link?

How is your query related to the sample data? And please define "duplicate". — Paul Spiegel
– Paul Spiegel, Commented Feb 18, 2017 at 0:12

Darshan Mehta · Accepted Answer · 2017-02-18 00:12:47Z

You can specify 3 columns in GROUP BY clause, e.g.:

SELECT name, price, link FROM users GROUP BY name, price, link HAVING COUNT(email) > 1;

Mary · Accepted Answer · 2017-02-18 00:15:04Z

You can group by more than one field at a time.

SELECT a, b, c, COUNT(d) FROM someTable GROUP BY a, b, c HAVING COUNT(d) > 1

Hope this helps.

Ricardo Ortega Magaña · Accepted Answer · 2017-02-18 00:17:15Z

Group by multiple columns, and the count the groups size:

SELECT name, price, link, Count(*) as cnt FROM table_name GROUP BY name, price, link where cnt>1

Collectives™ on Stack Overflow

Finding Duplicates with SQL?

3 Answers 3

Comments

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Related