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

2

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;

answered Feb 18, 2017 at 0:12

Darshan Mehta

31k14 gold badges75 silver badges111 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

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

2

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.

answered Feb 18, 2017 at 0:15

Mary

1015 bronze badges

Comments

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

1

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

answered Feb 18, 2017 at 0:17

Ricardo Ortega Magaña

2,44219 silver badges23 bronze badges

Collectives™ on Stack Overflow

Finding Duplicates with SQL?

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related